topic Re: Measure to calculate hours & $ of distinct value in a column in DAX Commands and Tips

Measure to calculate hours & $ of distinct value in a column

reachkrs — Wed, 06 Jul 2022 11:57:49 GMT

I am new to this forum & also to DAX. I have a Power BI model with data in columns A, B, C & D. I want to calculate the sum using a measure to sum as it appears in columns E & F. These are sums of columns C & D based on column A. Column A can repeat but I want to calculate it only once. Also, I want to calculate the Average & Median of these calculated measures. How to get this using DAX.

Seeking help from the community on how to address this and get the desired result.

Re: Measure to calculate hours & $ of distinct value in a column

amitchandak — Wed, 06 Jul 2022 17:10:36 GMT

reachkrs ,

Try measures like

Sumx(summarize(Table, Table[Project], Table[Hour]), [Hour])

Sumx(summarize(Table, Table[Project], Table[$]), [$])

Re: Measure to calculate hours & $ of distinct value in a column

reachkrs — Tue, 12 Jul 2022 08:44:42 GMT

Thank you Amit. Sumx works perfectly.

Is it possible to calculate average & median of these values ?. Since these are Measures Median, Average functions do not accept these as parameters.

Re: Measure to calculate hours & $ of distinct value in a column

v-cazheng-msft — Thu, 14 Jul 2022 12:15:00 GMT

Hi reachkrs ,

Not quite sure about the calculated logic of Sum Measure Result Hours. But you may try the following Measures.

Result $ Measure = VAR minLine = CALCULATE ( MIN ( 'Table'[Line] ), ALLEXCEPT ( 'Table', 'Table'[Project], 'Table'[Hour] ) ) VAR NotRepeat = IF ( MAX ( 'Table'[Line] ) = minLine, MAX ( 'Table'[$] ), 0 ) RETURN NotRepeat

Sum Measure Result $ = SUMX('Table',[Result $ Measure])

Average Sum $ = AVERAGEX('Table',[Sum Measure Result $])

Median Sum $ = MEDIANX('Table',[Sum Measure Result $])

The result looks like this.

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please let me know. Thanks a lot!

Best Regards,

Community Support Team _ Caiyun

Re: Measure to calculate hours & $ of distinct value in a column

reachkrs — Thu, 21 Jul 2022 08:57:59 GMT

Thank you. The code provided by you gives exact result.

This solves my problem.