Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to 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.
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.
hi @jd8766
try like:
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.
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |