Bar Chart Group By

[TimmK]

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".

[Anonymous · Accepted solution]

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.

[barritown]

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