Measure sum based on distinct count of another column

[Anonymous]

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.

[Anonymous]

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.

[Greg_Deckler]

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.

[Anonymous · Accepted solution]

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.