cancel
Showing results for
Did you mean: Frequent Visitor

## calculate % of total with filters on them

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:

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(ALL('Sales'[salessegment]),unit)
var alloc = DIVIDE(unit,grandtotal,0)
return
alloc

I am not getting the right answer on this one. The % allocation should look something like on the table above as allocation 1 and 2 column

Thank you.
1 ACCEPTED SOLUTION  Community Support

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.

5 REPLIES 5  Community Support

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. Frequent Visitor

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:

var unit= CALCULATE([Revenue YTD], FILTER('Sales', 'Sales'[Segment] = "Segment1" || 'Sales'[Segment] = "Segment2" ))
var grandtotal= SUMX(FILTER(ALLSELECTED('Sales'[Segment],'Sales'[Department]), 'Sales'[Segment] = "Segment1" || 'Sales'[Segment] = "Segment2"),[Revenue YTD])
var alloc = DIVIDE(unit,grandtotal,0)
return
alloc  Super User

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`````` Frequent Visitor

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.  Super User

I have little context of that...pm you my email, if you would like to share a sample file  