Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Anonymous
Not applicable

Measure sum based on distinct count of another column

Hello community

 

I made a measure to see if the min value is equal to max value. I need it to make a gauge visual to show the total number of distinct BARCODE and the total BARCODE with correct data.

 

Total BARCODE: 70657

Total DISTINCT BARCODE: 29466

SUM of correct data: 55624

 

DAX formula:

 

QPI_Amount =
VAR MinAmount = CALCULATE(MIN(ESPA_csv[TOTAL_AMOUNT_ORIGINAL]),ALLEXCEPT(ESPA_csv,ESPA_csv[BARCODE]))
VAR MaxAmount = CALCULATE(MAX(ESPA_csv[TOTAL_AMOUNT_ORIGINAL]),ALLEXCEPT(ESPA_csv,ESPA_csv[BARCODE]))
VAR CorrectAmount = IF( MinAmount=MaxAmount, "1", "0")
 
RETURN
CALCULATE( CorrectAmount,
DISTINCT(ESPA_csv[BARCODE]))
 

ESPA_csv

 

BARCODEStatus_WF_NOTOTAL_AMOUNT_ORIGINAL
71572107500
71572107700
71572107751880,74
71572107901880,74
71572107911880,74
71572107991880,74
71586230200
71586230300
71586230500
71586230700
7158623090290,98
7158623091290,98
7158623099290,98
71586233200
71586233300
71586233500
71586233700
715862339075,94
715862339175,94
715862339975,94
7159160820250
7159160830250
7159160890250
7159160891250
7159160895250

 

Thank you.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

it looks like my first reply was not posted for whatever reason.

 

meantime I created a new table with distinct BARCODES and made the calculated columns based on the new table. it worked.

 

Thank you.

View solution in original post

2 REPLIES 2
Greg_Deckler
Super User
Super User

Not sure what "correct data" means.

 

Distinct count of barcode would be:

 

DISTINCTCOUNT('ESPA_csv'[BARCODE]

 

I feel like perhaps you need to do a SUMMARIZE and then ADDCOLUMNS your QPI_Amount but in your QPI_Amount, RETURN CorrectAmount var and then you can aggregate over that table using COUNTX, etc.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

it looks like my first reply was not posted for whatever reason.

 

meantime I created a new table with distinct BARCODES and made the calculated columns based on the new table. it worked.

 

Thank you.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors