I'm having problems with a line chart calculating the percentage of subtotals. What I have at the moment is as follows:
The PowerBI Data is structured similarly with a few extra columns.
I want to do a line graph where for each quarter, I have the percentage of each result for that quarter. For example, In Q4, between Business Unit A and B, I have 45 Passes, 2 Partial Passes, and 19 Fails. so of a total of 66, Q1 should show 68% pass, 3% partial pass, and 29% Fail. When I do the chart in Excel, I get the following (which is what I want to replicate in PowerBI):
In order to build this in PowerBI, I'm using a measure with the following formula:
Percentage = DIVIDE(SUM('TABLE NAME'[Number]),CALCULATE(SUM('TABLE NAME'[NUMBER]),ALLEXCEPT('TABLE NAME'[DATE].[QUARTER])
When I use that formula, if I filter on an individual quarter, the chart values are correct, but when more than 1 quarter is displayed, the percentages are incorrect:
1 Quarter displayed:
Full Chart Displayed
The percentages between the chart in Excel and PowerBI are off when multiple quarters are displayed in PowerBI. I can't for the life of me figure out how to get the chart to be correct.
Any help would be tremendously appreciated.
Thanks!