Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
116 | |
102 | |
78 | |
76 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |