Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
105 | |
88 | |
74 | |
66 |
User | Count |
---|---|
124 | |
112 | |
98 | |
81 | |
72 |