Issue with Bar Chart Display When Switching Metrics in a Dynamic Slicer in Power BI

[Pier8891]

Problem Description

I am using a slicer in Power BI to toggle between two different metrics in a bar chart and a donut chart. The dynamic measure I am using is the following:

DAX

ChartValue = VAR SelectedOption = SELECTEDVALUE('Slicer 1'[Classification Delays], "Delay Minutes") RETURN SWITCH( SelectedOption, "Delay Quantities", [Nums OF DELAYS] + 0, [AVG MINS OF DELAYS] + 0 )

The slicer allows switching between:

• "Delay Quantities" → Represents the number of delays (Nums OF DELAYS).
• "AVG Delay Min" → Represents the average delay time in minutes (AVG MINS OF DELAYS).

However, when switching between these metrics, the bar chart becomes irregular and confusing. Below are screenshots of the issue:

📷 Image 1: When the slicer is set to "Delay Quantities", the bar chart correctly displays the number of delays per airport.
📷 Image 2: When switching the slicer to "AVG Delay Min", the bar chart becomes distorted—some bars are extremely small, while others are disproportionately large, making comparisons difficult.

 

[DataNinja777 · Accepted solution]

Hi @Pier8891 ,

The issue you are experiencing with the bar chart in Power BI occurs because the two metrics—Delay Quantities (a count-based measure) and AVG Delay Min (an average-based measure)—operate on drastically different scales. The number of delays per airport can be in the thousands, while the average delay time is typically in the tens or low hundreds. When switching between these metrics, the axis scale remains static, causing one measure to appear disproportionately large while the other becomes almost invisible.

A possible solution is to enable a logarithmic scale for the Y-axis in the bar chart, which helps balance the differences between large and small values by adjusting the scale dynamically. If a logarithmic scale does not provide a satisfactory visualization, another approach is to use separate measures for each metric and then create two bar charts—one displaying Nums OF DELAYS and another displaying AVG MINS OF DELAYS—while using Bookmarks and the Selection Pane to toggle between them based on the slicer selection.

Alternatively, if maintaining a single chart is preferred, normalization can be applied by scaling up the AVG Delay Min values to make them more visually comparable to Delay Quantities. This can be done by multiplying the average delay minutes by a factor such as 10, ensuring that it occupies a proportional space in the bar chart. The adjusted DAX formula for this approach is:

ChartValue = 
    VAR SelectedOption = SELECTEDVALUE('Slicer 1'[Classification Delays], "Delay Minutes") 
    RETURN SWITCH(
        SelectedOption,
        "Delay Quantities", [Nums OF DELAYS] + 0,
        "AVG Delay Min", [AVG MINS OF DELAYS] * 10
    )

This normalization method ensures that the bars remain visually distinguishable without distorting the actual data representation. If you would like guidance on implementing bookmarks for toggling between two separate charts, let me know, and I can provide step-by-step instructions.

Best regards,

[Pier8891]

Hi @DataNinja777,

Thank you for your detailed response! I tried implementing the logarithmic scale, but unfortunately, I couldn't get it to work properly. I also attempted normalizing the values by multiplying the AVG Delay Min by 10, but the issue persisted.

After further analysis, I believe the real problem is that the AVG Delay Min measure isn't actually an average, but rather the result of dividing the total delay minutes by the total number of delays. When I use a simple AVERAGE() function, the chart displays correctly, but when I use the division (SUM(Minutes) / SUM(Delays)), the chart becomes distorted when switching metrics.

It seems that the scaling issue might not be caused solely by the difference in magnitude between the two measures, but rather by how Power BI processes the division when switching between them. Do you have any recommendations on how to handle this properly?

Thanks again for your help!

Best regards,
Pier