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.
I am trying to calculate % of totals with filters on them as a measure to make the calculation dynamic. I have a calculated revenue measure and a list of departments and sales segments on a different table. I am trying to calculate what % of revenue should be allocated between the total of certain department. It should look something like this:
sales segment | Department | Values | allocation 1 and 2 |
segment 1 | department A | 10 | 10% |
segment 1 | department B | 15 | 15% |
segment 1 | department C | 20 | 20% |
Segment 2 | department D | 10 | 10% |
Segment 2 | department E | 15 | 15% |
Segment 2 | department F | 20 | 20% |
Segment 2 | department G | 10 | 10% |
Segment 3 | department H | 15 | |
Segment 3 | department I | 20 | |
Segment 3 | department J | 10 | |
Segment 3 | department K | 15 | |
segment 1 | 45 | ||
Segment 2 | 55 | ||
Segment 3 | 60 | ||
Segment 1 and 2 total | 100 |
Formula I have tried:
Solved! Go to Solution.
Hi @prakritnepal-cn ,
I create a simple example, please check if this could work in your scenario:
allocation % =
VAR RevenueYTD_ALLSELECTED =
SUMX ( ALLSELECTED ( Sales[sales segment], Sales[Department] ), [Revenue YTD] )
RETURN
DIVIDE ( [Revenue YTD], RevenueYTD_ALLSELECTED )
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @prakritnepal-cn ,
I create a simple example, please check if this could work in your scenario:
allocation % =
VAR RevenueYTD_ALLSELECTED =
SUMX ( ALLSELECTED ( Sales[sales segment], Sales[Department] ), [Revenue YTD] )
RETURN
DIVIDE ( [Revenue YTD], RevenueYTD_ALLSELECTED )
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Tweaked it a little bit to make it work: I couldn't use slicers as the calculation needed to be combined again. Formula I used:
try it
Segment 1 and 2 allocation % =
var unit= CALCULATE([Revenue YTD], FILTER('Sales', 'Sales'[sales segment] = "segment 1" ||'Sales'[sales segment] = "segment 2"))
var grandtotal= SUMX(FILTER(ALL('Sales'), 'Sales'[sales segment] = "segment 1" ||'Sales'[sales segment] = "segment 2"),[Revenue YTD])
var alloc = DIVIDE(unit,grandtotal,0)
return
alloc
That seems like it worked but the total is coming out incorrect. Instead of the % totals for that category coming out to be 100% it is at 99.57%.
The revenue YTD is a dynamic measure which changes based on slicer selection. Would that affect this? If I change the revenue YTD the allocation measure changes to lower amount:
It should be 100% based on the slicer selection all the time.
I have little context of that...pm you my email, if you would like to share a sample file
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 |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |