cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper V
Helper V

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

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

1 ACCEPTED SOLUTION
Super User I
Super User I

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]

 

 

Capture.PNGCapture2.PNG



Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


Proud to be a Super User helping give back to the community!
Thank You!




View solution in original post

6 REPLIES 6
Super User I
Super User I

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]

 

 

Capture.PNGCapture2.PNG



Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


Proud to be a Super User helping give back to the community!
Thank You!




View solution in original post

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

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.

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

 

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) ] )

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.

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors