Forum Discussion
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)
I want to build a matrix visual in PBI that shows me the below..
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?
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)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.
3 Replies
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.
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)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.
