Calculate % against total correctly?

[jd8766]

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?

[v-zhangti · Accepted solution]

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.

[FreemanZ]

hi @jd8766 
try like:

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

CALCULATE(SUM(Cost), ALL(UnsubscribeReasonKey))

[pacoduabe]

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.