none
controlling format in power pivot

    Question

  • Hi we run 2012 entwrprise.  Can the format of a cell be controlled by what another cell has?  eg if other cell has an A, this one shows as currency.  But if it contains a B, this one shows as just a number with 1000'ws sepaeator?  If yes, is this done in the pp window or in the pivot table?
    Thursday, January 30, 2014 4:52 AM

Answers

  • Hi,

    If you want to display nothing whenever there is more than one value in Data[Column1] in the example, you can try the following revised DAX formula:

    FormattedSumOfColumn2:=
      IF(
        HASONEVALUE(Data[Column1]),
        SWITCH(
          VALUES(Data[Column1]), 
          "A", FORMAT([SumOfColumn2], "£#,#"), 
          "B", FORMAT([SumOfColumn2], "#,#"), 
          BLANK()
        )
      )


    The inner BLANK() can be swapped for another default value and format if you don't want it to be blank in the case that the Data[Column1] value is neither "A" or "B". By not specifying a 3rd parameter for the if function, it will default to a blank value in any scenario when there is more than one value in Data[Column1] in a given filter context.

    Edit: Please note that I've revised the formula off the top of my head so there could potentially be syntax errors.


    Regards,

    Michael

    Please remember to mark a post that answers your question as an answer...If a post doesn't answer your question but you've found it helpful, please remember to vote it as helpful :)

    Website: nimblelearn.com, Blog: nimblelearn.com/blog

    • Edited by Michael AmadiEditor Tuesday, March 04, 2014 4:21 PM Additional info
    • Marked as answer by DB042189 Tuesday, March 04, 2014 9:10 PM
    Tuesday, March 04, 2014 4:17 PM
    Answerer

All replies

  • Hi DB042189,

    The only way I can think of achieving this within the Power Pivot model is by converting the numbers into text based on the conditional logic. I have provided a quick illustration of this approach below...

    Here is the very simple test data:

    Column1 Column2
    A 1000
    B 1000
    A 500
    B 700

    Here are the defined measures: 

    SumOfColumn2:=CALCULATE(SUM(Data[Column2]))

    And...

    FormattedSumOfColumn2:=
      IF(
        HASONEVALUE(Data[Column1]),
        SWITCH(
          VALUES(Data[Column1]), 
          "A", FORMAT([SumOfColumn2], "£#,#"), 
          "B", FORMAT([SumOfColumn2], "#,#"), 
          FORMAT([SumOfColumn2], "General Number")
        ), 
        FORMAT([SumOfColumn2], "General Number")
      )

    Here is the outcome:

    The idea here is that for charts you would use the measure that returns the actual numbers (i.e. SumOfColumn2 in the example), and for Pivot Tables you would use the measure that returns the formatted text (i.e. FormattedSumOfColumn2).

    You can read about the more 'interesting' functions out of the ones used in this solution:

    HASONEVALUE:http://technet.microsoft.com/en-us/library/gg492190.aspx

    VALUES: http://technet.microsoft.com/en-us/library/ee634547.aspx

    SWITCH: http://technet.microsoft.com/en-us/library/gg492166.aspx

    FORMAT: http://technet.microsoft.com/en-us/library/ee634924.aspx

    Hope this helps,

    Michael


    Saturday, February 08, 2014 1:41 AM
    Answerer
  • interesting but would i give up the natural aggregation capabilities in the pivot table itself?
    Monday, February 10, 2014 2:33 PM
  • If by 'natural aggregation capabilities' you mean the ability to slice and dice the measure by various attributes/columns, then no, you wouldn't be giving this up. The measure calculation is happening before it is formatted to text. If you look at my example above, you'll notice that the measure values calculated have been aggregated before being formatted, so we get  £1,500 for A instead of 2 rows of £1,000 and £500, for example.

    If you meant something else, could you kindly provide an example to clarify?

    Alternatively, you could use Excel's built-in conditional formatting abilities but this would bind the formatting to whatever is currently being shown (i.e. cells) at that point in time so doesn't work too well with the dynamics nature of pivot tables (i.e. growing, shrinking, etc).



    Monday, February 10, 2014 3:05 PM
    Answerer
  • i'll build an example with two fact tables, sales and employee.  Both will have fks to fiscal quarter and region.  I'll fashion a pivot that combines the $s and counts into one pivot table whose labels should determine how the numeric values should display.  I'll post results here.

    I'm a bit concerened that I couldnt get hasonevalue to work as explained at http://social.msdn.microsoft.com/Forums/en-US/7e0f1e92-1d24-4bfb-bdfa-85ff83c480dd/simulating-drill-thru-without-totals-in-one-column?forum=sqlkjpowerpivotforexcel  .   But that could be because the purpose of that function hasnt sunk in yet. 

    Monday, February 10, 2014 6:37 PM
  • Hi,

    Did you get round to giving this solution a try?


    Regards,

    Michael

    Please remember to mark a post that answers your question as an answer...If a post doesn't answer your question but you've found it helpful, please remember to vote it as helpful :)

    Thursday, February 27, 2014 12:48 PM
    Answerer
  • no sorry.  I'm swamped.  I'll try to get to this saturday.
    Thursday, February 27, 2014 1:18 PM
  • i have this started, where / how are these defined measures created?  I think we've talked about calc'd cols and  calc'd fields but not defined measures yet.  I am searching the web but am still looking.   From the name it sounds like it is done in the pivot area, not the model but the dax makes me wonder.
    Monday, March 03, 2014 5:33 PM
  • A measure is just another name for a calculated field. You can create them in the calculation area found in the Power Pivot window.

    Take a look at this article which specifically addresses calculated fields in Power Pivot: http://office.microsoft.com/en-gb/excel-help/calculated-fields-in-power-pivot-HA102837191.aspx


    Regards,

    Michael

    Please remember to mark a post that answers your question as an answer...If a post doesn't answer your question but you've found it helpful, please remember to vote it as helpful :)

    Monday, March 03, 2014 6:38 PM
    Answerer
  • this appears to have worked "partially".  I'll have to post separately how hybrid totals can be suppressed.  

    I created a sales fact table and an inventory fact table and "unioned" a select on them.  I had to change the name "data" to "Quey" because apparently thats what pp called my model's resultset by default. 

    My sales and inventory numbers look good but at the first level of row labels where both sales and inventory are subordinate, only sales appears to be totalled and without the special formatting.  I actually wouldnt want either totalled at that line or higher (eg grand tots) but otherwise this seems to work.

    To me this opens up some workarounds to shortcomings that are in ssrs.   Thx.   I'll mark the post answered in a few minutes after (if you are game) you've had a chance to respond to the hybrid total question  .

    Monday, March 03, 2014 7:33 PM
  • Hi,

    If you want to display nothing whenever there is more than one value in Data[Column1] in the example, you can try the following revised DAX formula:

    FormattedSumOfColumn2:=
      IF(
        HASONEVALUE(Data[Column1]),
        SWITCH(
          VALUES(Data[Column1]), 
          "A", FORMAT([SumOfColumn2], "£#,#"), 
          "B", FORMAT([SumOfColumn2], "#,#"), 
          BLANK()
        )
      )


    The inner BLANK() can be swapped for another default value and format if you don't want it to be blank in the case that the Data[Column1] value is neither "A" or "B". By not specifying a 3rd parameter for the if function, it will default to a blank value in any scenario when there is more than one value in Data[Column1] in a given filter context.

    Edit: Please note that I've revised the formula off the top of my head so there could potentially be syntax errors.


    Regards,

    Michael

    Please remember to mark a post that answers your question as an answer...If a post doesn't answer your question but you've found it helpful, please remember to vote it as helpful :)

    Website: nimblelearn.com, Blog: nimblelearn.com/blog

    • Edited by Michael AmadiEditor Tuesday, March 04, 2014 4:21 PM Additional info
    • Marked as answer by DB042189 Tuesday, March 04, 2014 9:10 PM
    Tuesday, March 04, 2014 4:17 PM
    Answerer