Forum Discussion
Create a custom measure based on matrix
I have the following matrix:
What I want is to get the following calculation as a number (%):
Calc = (Count of Promoter - Count of Detractor)/Total (%)
How do I do this, to display it as a card?
Anonymous try this:
Total Count = COUNTROWS ( YourTable ) Calc = DIVIDE ( CALCULATE ( [Total Count], YourTable[NPS_Segment] = "Promoter" ) - CALCULATE ( [Total Count], YourTable[NPS_Segment] = "Detractor" ), [Total Count] )✨ Follow us on LinkedIn and to our YouTube channel
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.
12 Replies
Anonymous try this:
Total Count = COUNTROWS ( YourTable ) Calc = DIVIDE ( CALCULATE ( [Total Count], YourTable[NPS_Segment] = "Promoter" ) - CALCULATE ( [Total Count], YourTable[NPS_Segment] = "Detractor" ), [Total Count] )✨ Follow us on LinkedIn and to our YouTube channel
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.
Thanks for the repy parry2k . However, the calculation doesn't seem correct. It doesn't give me the expected result (expected output = 46.07%, actual output = 2.98%).
I checked, and for example each calculation within the divide does not give me the correct value. For example,CALCULATE ( 'NPS_Redeemers'[Total Count], 'NPS_Redeemers'[NPS_SEGMENT] = "Promoter" ) evaluate to 397 when it should actually just be equal to the Count of Promoter from the matrix, i.e. 5402.
Anonymous Can you post sample raw data in the table format? It has something to do with the data structure.
Anonymous the formula I gave you, how you try to visualize it? In a card visual?
Anonymous then it should work, what is TOTAL COUNT? Is it a measure? What is the expression for this measure?
Anonymous when you added this measure and visualize in the card, you are not getting correct result? Can you confirm?
CALCULATE ( [Total Count], 'NPS_Redeemers'[NPS_SEGMENT] = "Promoter" )Anonymous weird, are you ok to share the pbix file, remove any sensitive information before sharing, just keep the basic data.