The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Solved! Go to 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]
Proud to give back to the community!
Thank You!
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]
Proud to give back to the community!
Thank You!
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
94 | |
93 | |
88 | |
70 | |
65 |
User | Count |
---|---|
231 | |
127 | |
117 | |
82 | |
82 |