Forum Discussion
Bar Chart Group By
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".
- Anonymous3 years ago
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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
- AnonymousNot applicable
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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.