cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## Create measure of distinct count

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
2 ACCEPTED SOLUTIONS
Super User

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

Community Support

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.

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.

2 REPLIES 2
Community Support

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.

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.

Super User

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors