Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
KasperJ90
Helper III
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
ClientMaster No_Project Code Group ClientDiscount count of Master No_Hit Rate (SUM of DISTINCT COUNT of BULK ORDER/SUM OF DISTINCT COUNT)
JK100005BULK ORDER JK23 / 4 = 75%
JK100005BULK ORDER PO21 / 2 = 50%
JK100005BULK ORDER    
JK100099BULK FORECAST    
PO100006BULK ORDER    
PO100007BULK FORECAST    
2 ACCEPTED SOLUTIONS
selimovd
Super User
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
 

 

View solution in original post

v-robertq-msft
Community Support
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:

v-robertq-msft_0-1623296137269.png

 

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.

View solution in original post

2 REPLIES 2
v-robertq-msft
Community Support
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:

v-robertq-msft_0-1623296137269.png

 

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
Super User
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
 

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.