How to display each month as 100% instead of the grand total in a bar chart?

[Anonymous]

Hello everyone,

I'm working with two tables:

1) or_main - contains a date field [DATE_refer] with a format "yyyymm" and [STATUS] field ("abierto", "notificado","no notificado").

2)or_operations - contains a measure QTY base on COUNTROWS()

I want to show, for each month (e.g., "202412"), how many operations fall under each STATUS and display this in a bar chart, such that each month's bar totals 100%. However , when I set up the bar chart to show percentages, it shows the percentages of the grand total instead of 100% per month 

QUESTION:

• How can I configure or calculate a measure so that each month sums up 100%, with each STATUS shown as  percentage of that month's total? 
• Do I need an additional DAX measure to calculate this percentage per month? If, so could you please suggest how to write it?

Thanks in advance for your help!

[HarshSathwara19]

Try the following steps:

1. Create a relationship: Ensure or_main and or_operations tables are related through a shared key (e.g., ID or Date table). If not, use TREATAS for a virtual relationship.
2. Calculate the monthly total operations: Create a measure to calculate the total operations for each month:

   TotalOperationsPerMonth = 
   CALCULATE(
       [QTY], 
       ALLEXCEPT(or_main, or_main[DATE_refer])
   )
       

3. Calculate the percentage for each STATUS: Create another measure:

   PercentagePerStatus = 
   DIVIDE(
       [QTY], 
       [TotalOperationsPerMonth], 
       0
   )
       

   • DIVIDE is used to handle division by zero safely.
   • [QTY] counts rows for the operations.
4. Add the measures to your visual:
   • Set DATE_refer on the Axis.
   • Place STATUS on the Legend.
   • Use PercentagePerStatus as the Value.
   • Format the chart as a 100% stacked bar chart.

Explanation:

• The TotalOperationsPerMonth measure calculates total operations per month, ignoring filters except DATE_refer.
• The PercentagePerStatus measure calculates the percentage of each STATUS for the total operations in the month.

If this post helps, please accept it as the solution to help others find it more quickly.

Best Regards,

Harsh Sathwara.

[HarshSathwara19]

Try the following steps:

1. Create a relationship: Ensure or_main and or_operations tables are related through a shared key (e.g., ID or Date table). If not, use TREATAS for a virtual relationship.
2. Calculate the monthly total operations: Create a measure to calculate the total operations for each month:

   TotalOperationsPerMonth = 
   CALCULATE(
       [QTY], 
       ALLEXCEPT(or_main, or_main[DATE_refer])
   )
       

3. Calculate the percentage for each STATUS: Create another measure:

   PercentagePerStatus = 
   DIVIDE(
       [QTY], 
       [TotalOperationsPerMonth], 
       0
   )
       

   • DIVIDE is used to handle division by zero safely.
   • [QTY] counts rows for the operations.
4. Add the measures to your visual:
   • Set DATE_refer on the Axis.
   • Place STATUS on the Legend.
   • Use PercentagePerStatus as the Value.
   • Format the chart as a 100% stacked bar chart.

Explanation:

• The TotalOperationsPerMonth measure calculates total operations per month, ignoring filters except DATE_refer.
• The PercentagePerStatus measure calculates the percentage of each STATUS for the total operations in the month.

If this post helps, please accept it as the solution to help others find it more quickly.

Best Regards,

Harsh Sathwara.

[bhanu_gautam]

@Anonymous , You will need to create a DAX measure that calculates the percentage of each STATUS for each month. Here’s how you can do it:

QTY = COUNTROWS(or_operations)

Create a measure to calculate the total operations for each month:

DAX
TotalOperationsPerMonth =
CALCULATE(
COUNTROWS(or_operations),
ALLEXCEPT(or_main, or_main[DATE_refer])
)

Create a measure to calculate the percentage of each STATUS for each month:

DAX
PercentagePerStatus =
DIVIDE(
COUNTROWS(or_operations),
CALCULATE(
COUNTROWS(or_operations),
ALLEXCEPT(or_main, or_main[DATE_refer])
),
0
)

Configure your bar chart:
X-axis: Set to or_main[DATE_refer] and ensure it is formatted to show the month.
Y-axis: Use the PercentagePerStatus measure.
Legend: Set to or_main[STATUS].

[Anonymous]

Hi @bhanu_gautam,

Thank you for the detailed explanation. I tried following the steps you outlined to calculate the percentage of each STATUS by month, but I’m still having trouble getting the bar chart to reflect the total percentage correctly.

The issue I’m facing is that I can’t seem to get the percentages to add up to 100% for each month. The chart displays individual STATUS values, but the sum of the percentages for each month doesn’t reach 100%.

Here’s what I have done so far:
1. I created the TotalOperationsPerMonth measure as you suggested:

TotalOperationsPerMonth =
CALCULATE(
   COUNTROWS(or_operations),
   ALLEXCEPT(or_main, or_main[DATE_refer])
)

2. Then, I implemented the PercentagePerStatus measure as follows:

PercentagePerStatus =
DIVIDE(
   COUNTROWS(or_operations),
   CALCULATE(
       COUNTROWS(or_operations),
       ALLEXCEPT(or_main, or_main[DATE_refer])
   ),
   0
)

3. I configured the chart:

• X-axis: or_main[DATE_refer] (formatted to show the month).
• Y-axis: PercentagePerStatus.
• Legend: or_main[STATUS].

However, the percentage per month isn’t distributed correctly. Could there be an additional configuration or measure I’m missing to ensure the percentages sum to 100% for each month?

I’d really appreciate any suggestions or insights you can provide.

Best regards,
Flor

Let me know if you need further adjustments!

[Anonymous]

Hi @Anonymous ,hello bhanu_gautam, thank you for your prompt reply!

What's the relationship between or_main and or_operations?

If possible, please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.