Calculation of two column values considering duplicates

ZickZack — Fri, 25 Aug 2023 08:54:34 GMT

Hi all,

I have data in an Excel spreadsheet. The first thing I want to do is find out the count of all unique VINs per model. I do this with the measure = DISTINCTCOUNT. Then I want to multiply the summed number of vehicles with the net EB for the specific model. Example: Model SZ has a net EB of 1200. The clear number of vehicles for model SZ is 2. Then the calculation should be: 2*1200. When I calculate this in Power Bi, the duplicates are always included in the calculation. Why?

This is my data model

Modell	Fahrgestell	net EB
SZ	123	1200
SZ	123	1200
SZ	123	1200
SZ	888	1200
SH	234	4500
SY	543	2351
SH	665	1351

I want to represent that in Power BI:

Modell	number of vehicles	net EB	number of vehicles * Netto EB
SZ	2	1200	2400
SH	2	4500	9000
SY	1	2351	2351

And that is what i get in Power BI:

My Measure for Sales Volumen is:

Sales Volumen = [number of vehicles] * SUMX(Table,Table[net EB])

I'm not very familiar with DAX and can't find my mistake. Can you help?

Thankx a lot

Re: Calculation of two column values considering duplicates

Anonymous — Mon, 28 Aug 2023 08:05:46 GMT

Hi ZickZack ,

You can update the formula of your measure as below to get it, please find the details in the attachment.

Sales Volumen = VAR _tab = SUMMARIZE ( 'Table', 'Table'[Modell], 'Table'[Fahrgestell], 'Table'[net EB] ) RETURN SUMX ( _tab, [net EB] )

Best Regards

topic Re: Calculation of two column values considering duplicates in DAX Commands and Tips

Calculation of two column values considering duplicates

Re: Calculation of two column values considering duplicates