Clustered bar chart with Date as X-Axis with dynamic granulometry

[Thomas_Salvan]

Hello

I'm struggelling with a cluster bart chart in wich I would like to see some data I have in a matrix.

I my Matrix, I have differnte kind of Events, link to a date, and every month, I have a number of event, every year a avergae number of event per month :

With the same set of data, I would like to have it in a Cluster bar chart

I have this : 

I can drill through, but it means like selecting a year.

I would like to be able to keep in the visual the other years, but to have the granularity of the month for the selected Years.

I try to have a second calendar, to be able to select a year without changing the overall set of data, but I cannot figure out how to build the X-axis with date showing some time year, some time month. As in the first Matrix, basically.

Thanks for your support

Thomas

[lbendlin · Accepted solution]

You are trying to do too much.  The standard column chart visual can only drill through to the next level or expand the entire level.

Use a custom visual or rethink what you are trying to achieve, and select a different visual.

Power BI is not Excel.

[Anonymous · Accepted solution]

Hi @Thomas_Salvan ,

Thanks for the reply from lbendlin .

I understand your needs now. For example, you want to show the detail data for 2023 when 2023 is checked, and the other years are shown with the same granularity, right?

Here's my test data:

Please create three calculated columns:

Month = FORMAT('Table'[Date],"mmmm")

Month_number = MONTH('Table'[Date])

Year = YEAR('Table'[Date])

Create a new table:

Table 2 =
UNION (
    SUMMARIZE (
        SELECTCOLUMNS (
            'Table',
            "_Month/Year", 'Table'[Month],
            "_Month/Year_Number", 'Table'[Month_number]
        ),
        [_Month/Year],
        [_Month/Year_Number]
    ),
    ADDCOLUMNS (
        SUMMARIZE (
            SELECTCOLUMNS ( 'Table', "_Month/Year", 'Table'[Year] ),
            [_Month/Year]
        ),
        "_Month/Year_Number", [_Month/Year]
    )
)

Please create a measure within this newly created table.

Measure =
VAR _Slicer =
    SELECTEDVALUE ( 'Table'[Date] )
RETURN
    IF (
        NOT ISFILTERED ( 'Table'[Year] ),
        CALCULATE (
            SUM ( 'Table'[Event Count] ),
            SELECTEDVALUE ( 'Table 2'[_Month/Year_Number] ) = 'Table'[Year]
        ),
        IF (
            SELECTEDVALUE ( 'Table 2'[_Month/Year_Number] ) <= 12,
            CALCULATE (
                SUM ( 'Table'[Event Count] ),
                'Table'[Month_number] = SELECTEDVALUE ( 'Table 2'[_Month/Year_Number] )
            ),
            IF (
                SELECTEDVALUE ( 'Table 2'[_Month/Year_Number] )
                    <> SELECTEDVALUE ( 'Table'[Year] ),
                CALCULATE (
                    SUM ( 'Table'[Event Count] ),
                    SELECTEDVALUE ( 'Table 2'[_Month/Year_Number] ) = 'Table'[Year]
                )
            )
        )
    )

Have the “_Month/Year” column in Table 2 sorted by the “_Month/Year_Number” column so that it is displayed without error in the visualization.

Use the Year column of the original table as the filter field for slicer.

Please create clustered column chart. Put the “_Month/Year” column on the X-axis, Measure on the Y-axis, and the “Event type” column on the end, the final visual effect is shown below:

The pbix file is attached.

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

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 feel free to let us know. Thanks a lot!

[lbendlin · Accepted solution]

You are trying to do too much.  The standard column chart visual can only drill through to the next level or expand the entire level.

Use a custom visual or rethink what you are trying to achieve, and select a different visual.

Power BI is not Excel.

[Thomas_Salvan]

Thanks a lot for your help

I made 2 small modifications : 

- I add a column to be able to sort the table _Month/Year starting with Year, then Month : 

Column = if(
    'Table 2'[_Month/Year_Number]<2000, 
    'Table 2'[_Month/Year_Number]*10000,
    'Table 2'[_Month/Year_Number])

Sorting the table like that give me first the years, then the months.

- I change :

SELECTEDVALUE ( 'Table 2'[_Month/Year_Number] ) <> SELECTEDVALUE ( 'Table'[Year] )

To

SELECTEDVALUE ( 'Table 2'[_Month/Year_Number] ) < SELECTEDVALUE ( 'Table'[Year] )

It hides the years after the selection. As I compare months to the previous years, I don't need the next years.

Thanks a lot again