calculation which does not affected by a category, but still affected by filter slicer when applied

[RMV]

Hi all,

I need to calculate % Cost / Revenue per Cost Category

I have a raw data table as follow

Date           Cost/Revenue      Cost Category     Amount

1-Jan-17     Cost                     A                          100

1-Jan-17     Revenue                                           120

2-Jan-17     Cost                     B                           50

3-Jan-17     Cost                     C                           20

3-Jan-17     Revenue                                           50

1-Feb-17    Cost                      A                          20

1-Feb-17    Revenue                                            70

3-Feb-17    Cost                      C                           40

Then I need to calculate the % Cost/Revenue per Cost Category, with revenue that is not affected by Cost Category, but still affected by Slicer of 2 other fields like Entity & Month

The result I expected is

Cost Category           %Cost/Revenue

A                                 50% ((100+20) / (120+50+70))

B                                 20.83% (50 / (120+50+70))

C                                 25% ((20+40) / (120+50+70))

And when I apply a filter on Month slicer = February, the result would be

Cost Category           %Cost/Revenue

A                                 28.57% (20 / 70)

C                                 57.14% (40 / 70)

Need advise please

[fhill · Accepted solution]

Here you go!  Lines show Interaction boarders...

calc_Revenus = CALCULATE(SUM(Table1[  Amount]),  // SUM Amount
      FILTER(ALLEXCEPT(Table1,Table1[Date    ]),    // Process any SLICER effects to the Date Column

      Table1[  Cost/Revenue    ] = "Revenue"))       //  Cost/Revenue = "Revenue" only

You can easily combine these two if you want one column. 

 % = SUM(Table1[  Amount]) / [calc_Revenus]

[RMV]

Hi @fhill, it works! Thanks a lot for your advise.

[Datatouille]

Hi,

First, you need to go back to the query editor and fill down your 'Cost Category' Column (Right Click on the column, Fill > Down). Once you've done this step, you can 'close and apply' the steps in the query editor.

Then, create the following measures:

Total Amount = Sum(YourTable[Amount])

Total Cost = Calculate ( [Total Amount] , YourTable[Cost/Revenue] = "Cost")

Total Revenue= Calculate ( [Total Amount] , YourTable[Cost/Revenue] = "Revenue")

Pct Cost = Divide ( [Total Cost] , [Total Revenue] )

Format [Pct Cost] as a percentage and use this measure in any of the visuals that you want to slice by Cost Category and/or month.

[RMV]

Hi @Datatouille, the blank Cost Category is intended blank, thus I don't need them to be filled with the value from the previous row.

The Revenue must not be affected by the Cost Category.

Thus, when I created a table or any other visual by Cost Category, total Revenue is only affected by Month slicer or any other slicer, lets say Entity.

Advise, please

[Datatouille]

Oops sorry !

This measure should work :

Total Revenue (All Cat) = Calculate ( [Total Revenue] , All(YourTable[Cost Category] ) )

And then

Pct Cost = Divide ( [Total Cost] , [Total Revenue (All Cat) ] )

[RMV]

It doesn't work, when I applied Month Slicer or Entity Slicer I have.

It only shows the total amount of all Revenue.

Please check the last example in my first post for what I'm referring to.