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

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

 BARCODE Status_WF_NO TOTAL_AMOUNT_ORIGINAL 71572107 50 0 71572107 70 0 71572107 75 1880,74 71572107 90 1880,74 71572107 91 1880,74 71572107 99 1880,74 71586230 20 0 71586230 30 0 71586230 50 0 71586230 70 0 71586230 90 290,98 71586230 91 290,98 71586230 99 290,98 71586233 20 0 71586233 30 0 71586233 50 0 71586233 70 0 71586233 90 75,94 71586233 91 75,94 71586233 99 75,94 71591608 20 250 71591608 30 250 71591608 90 250 71591608 91 250 71591608 95 250

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.

2 REPLIES 2
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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
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.