topic Re: Bar Chart Group By in DAX Commands and Tips

Bar Chart Group By

TimmK — Tue, 25 Jul 2023 13:15:07 GMT

I have the simple FACT table "Piece" with the columns "Piece Key" and "Duration". A piece key may occur multiple times (as seen in the left table below). Yet, I want to group by "Piece Key" to get the summed duration per piece (as seen in the right table below; 407436 is summed to 60)

This works fine so far.

The DAX code I use for grouping by "Piece Key" is the following:

Duration SUM = CALCULATE( SUMX( SUMMARIZE('Piece', 'Piece'[Piece Key], "SumDuration", SUM('Piece'[Duration])), [SumDuration] ) )

Yet, I want to show the distribution in the sense of frequency per summed duration in a bar chart. But it does not work, I cannot drag & drop my measure with the summed duration by piece key into the x-axis field. How can I do this?

I can only use duration for each row, so it counts the value "30" twice, instead of summing it up to "60" and then counting "1".

Re: Bar Chart Group By

barritown — Wed, 26 Jul 2023 12:12:42 GMT

Hello TimmK,

Why don't you just create an additional table and use its [SUM Duration] column to create the bar chart you need?

Extra Table = SUMMARIZE ( Piece, [Piece Key], "SUM Duration", SUM ( Piece[Duation] ) )

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

Re: Bar Chart Group By

Anonymous — Thu, 27 Jul 2023 03:14:52 GMT

Hi TimmK ,

You need to create a table with data generateseries from 0 to max value (60) for X aixs in column chart.

X aixs = VAR _SUMMARIZE = SUMMARIZE ( ALL ( 'Piece' ), 'Piece'[Piece Key], "SumDuration", SUM ( 'Piece'[Duration] ) ) RETURN GENERATESERIES ( 0, MAXX ( _SUMMARIZE, [SumDuration] ), 1 )

Measure:

Count = VAR _SUMMARIZE = SUMMARIZE ( ALL ( 'Piece' ), 'Piece'[Piece Key], "SumDuration", SUM ( 'Piece'[Duration] ) ) RETURN COUNTAX ( FILTER ( _SUMMARIZE, [SumDuration] = MAX ( 'X aixs'[X aixs] ) ), [Piece Key] )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.