Forum Discussion

TimmK's avatar
TimmK
Icon for Helper IV rankHelper IV
3 years ago
Solved

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

  • Anonymous's avatar
    Anonymous
    3 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 Zhou

     

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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not 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 Zhou

     

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