Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jd8766
Helper II
Helper II

Calculate % against total correctly?

Hi, hopefully someone can advise if this can be done.

I have 2 tables in PBI, one is a subscripton fact table and one is an unsubscribe reason dimension table. (example below)

jd8766_1-1677847769397.png

 


I want to build a matrix visual in PBI that shows me the below..

jd8766_2-1677847884517.png



This is all the unsubscribe reasons, then a % of the [Cost] column associated to these unsubscribe reasons. The calc for this is 

CALCULATE(SUM(Cost),UnsubscribeReasonKey<>-1) / SUM(Cost)

However when I use this in my visual.. it shows everything as 100%.. when really I want it to show me the % of the [cost] per UnsubscribeReason vs the TOTAL cost in the Subscriptions table?

1 ACCEPTED SOLUTION
v-zhangti
Community Support
Community Support

Hi, @jd8766 

 

You can try the following methods.

Measure = 
Var _N1=CALCULATE ( SUM ( Subscriptions[Cost] ),
    FILTER ( ALL ( Subscriptions ), [UnsubscribeReasonKey] <> -1 ),
    FILTER ( ALL ( UnsubscribeReason ), [Reason] = SELECTEDVALUE ( UnsubscribeReason[Reason] )))
Var _N2=CALCULATE(SUM(Subscriptions[Cost]),ALL(Subscriptions))
Return
DIVIDE(_N1,_N2)

vzhangti_0-1678158904849.png

Is this the result you expect?

 

Best Regards,

Community Support Team _Charlotte

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

3 REPLIES 3
v-zhangti
Community Support
Community Support

Hi, @jd8766 

 

You can try the following methods.

Measure = 
Var _N1=CALCULATE ( SUM ( Subscriptions[Cost] ),
    FILTER ( ALL ( Subscriptions ), [UnsubscribeReasonKey] <> -1 ),
    FILTER ( ALL ( UnsubscribeReason ), [Reason] = SELECTEDVALUE ( UnsubscribeReason[Reason] )))
Var _N2=CALCULATE(SUM(Subscriptions[Cost]),ALL(Subscriptions))
Return
DIVIDE(_N1,_N2)

vzhangti_0-1678158904849.png

Is this the result you expect?

 

Best Regards,

Community Support Team _Charlotte

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

FreemanZ
Super User
Super User

hi @jd8766 
try like:

CALCULATE(SUM(Cost),UnsubscribeReasonKey<>-1) / 
CALCULATE(SUM(Cost), ALL(UnsubscribeReasonKey))

 

Your visual has a context, for example: a table with row field as "UnsubscribeReasonKey".

 

The first row is for 1, every SUM done in this row will filter by "UnsubscribeReasonKey" = 1, so it would be SUM("UnsubscribeReasonKey" = 1) / SUM("UnsubscribeReasonKey" = 1). Unless, you use ALL as shown in the previous response.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.