Create a custom measure based on matrix

[Anonymous]

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?

[parry2k]

@Anonymous weird, are you ok to share the pbix file, remove any sensitive information before sharing, just keep the basic data.

[Anonymous]

Ok this is completely my mistake - I had two tables named very similarly and I was mixing up the values from each one.

Your solution works just fine now that this has been sorted!! Thank you for your patience

[parry2k]

@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]

Exactly, adding that as a measure should theoretically give me row 1 of the matrix in the question (i.e. 5402) but it gives me 397

[parry2k]

@Anonymous then it should work, what is TOTAL COUNT? Is it a measure? What is the expression for this measure?

[Anonymous]

Yes, I created Total Count as a measure as specified in your first reply 

Total Count = COUNTROWS ( Table  )

 which if I visualise it in a card, it gives the correct number (7957). The problem seems to be in the calculations within the DIVIDE

[parry2k]

@Anonymous the formula I gave you, how you try to visualize it? In a card visual?

[Anonymous]

Yes, in a card

[parry2k]

@Anonymous Can you post sample raw data in the table format? It has something to do with the data structure.

[Anonymous]

Here is some sample data - note that MSISDN contains unique customer IDs

MSISDN  NPS_SEGMENT

123          Detractor

234          Passive

345          Promoter

456          Promoter

567          Detractor

[parry2k · Accepted solution]

@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.

[Anonymous]

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.