cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
prakritnepal-cn
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 segmentDepartmentValuesallocation 1 and 2
segment 1department A1010%
segment 1department B1515%
segment 1department C2020%
Segment 2department D1010%
Segment 2department E1515%
Segment 2department F2020%
Segment 2department G1010%
Segment 3department H15 
Segment 3department I20 
Segment 3department J10 
Segment 3department K15 
    
 segment 145 
 Segment 255 
 Segment 360 
    
 Segment 1 and 2 total100 

 

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
Icey
Community Support
Community Support

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 )

revenue.gif

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
Icey
Community Support
Community Support

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 )

revenue.gif

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

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

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

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%. 

prakritnepalcn_0-1626873474925.png

 

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:

prakritnepalcn_1-1626873595497.png

 

It should be 100% based on the slicer selection all the time.

 

 

 

Hi @prakritnepal-cn 

 

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

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!