Create measure of distinct count

[KasperJ90]

Hi all

I have some DATA where I need to calculate a Hit Rate using distinct values.

Can you help based on below where I shared how the data looks and what I need (Hit Rate)?

DATA		RESULT
Client	Master No_	Project Code Group		Client	Discount count of Master No_	Hit Rate (SUM of DISTINCT COUNT of BULK ORDER/SUM OF DISTINCT COUNT)
JK	100005	BULK ORDER		JK	2	3 / 4 = 75%
JK	100005	BULK ORDER		PO	2	1 / 2 = 50%
JK	100005	BULK ORDER
JK	100099	BULK FORECAST
PO	100006	BULK ORDER
PO	100007	BULK FORECAST

[selimovd · Accepted solution]

Hey @KasperJ90 ,

try the following mesaure:

Distinct Master_No = 
VAR vMasterNo = MAX( myTable[Master No_] )
VAR vProjectCodeGroup = MAX( myTable[Project Code Group] )
VAR vMasterNoRows = CALCULATE( COUNTROWS(myTable), myTable[Master No_] = vMasterNo )
VAR vProjectCodeGroupRows = CALCULATE( COUNTROWS(myTable), myTable[Project Code Group] = vProjectCodeGroup )

RETURN
    DIVIDE(vProjectCodeGroup , vProjectCodeGroupRows)

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[v-robertq-msft · Accepted solution]

Hi, @KasperJ90 

According to your description and sample data, I can clearly understand your requirement, you can try these two measures to achieve this:

Discount count of Master No_ =

CALCULATE(DISTINCTCOUNT('Table'[Master No_]),FILTER(ALL('Table'),[Client]=MAX('Table'[Client])))

Hit Rate =

var _sumofdistinct=COUNTX(FILTER(ALL('Table'),[Client]=MAX('Table'[Client])&&[Project Code Group]="BULK ORDER"),[Client])

var _sum=COUNTX(FILTER(ALL('Table'),[Client]=MAX('Table'[Client])),[Client])

return

DIVIDE(_sumofdistinct,_sum)

Then you can create a table chart to place it like this:

And you can get what you want.

You can download my test pbix file below

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[selimovd · Accepted solution]

Hey @KasperJ90 ,

try the following mesaure:

Distinct Master_No = 
VAR vMasterNo = MAX( myTable[Master No_] )
VAR vProjectCodeGroup = MAX( myTable[Project Code Group] )
VAR vMasterNoRows = CALCULATE( COUNTROWS(myTable), myTable[Master No_] = vMasterNo )
VAR vProjectCodeGroupRows = CALCULATE( COUNTROWS(myTable), myTable[Project Code Group] = vProjectCodeGroup )

RETURN
    DIVIDE(vProjectCodeGroup , vProjectCodeGroupRows)

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic