calculate % of total with filters on them

[prakritnepal-cn]

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.

[Icey · Accepted 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.

[prakritnepal-cn]

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

[Vera_33]

Hi @prakritnepal-cn 

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

[prakritnepal-cn]

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.

[Vera_33]

Hi @prakritnepal-cn 

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