Power BI Drill Down in Line Chart When data doesn't sum to top level value

[Andrewdj]

I've been asked to create a line chart to show a Quality score over the last 10 years and the user wants to be able to drill into that Score and see the 4 sub scores that make up that value in the same line chart.

The issue is the sub scores don't sum equaly to the Quality score. The sub scores all have different weights applied to make up the Quality score. So think of it like this:

QualityScore1: 30%

QualityScore2: 30%

QualityScore3: 20%

QualityScore4: 20%

The data is in table like this:

Date	QualitySubScores	Value
1/31/2014	QualityScore1	6.191682168
1/31/2014	QualityScore2	1.505252591
1/31/2014	QualityScore3	-3.299236459
1/31/2014	QualityScore4	-0.330148055
2/28/2014	QualityScore1	6.738077186
2/28/2014	QualityScore2	2.221938009
2/28/2014	QualityScore3	-3.0676829
2/28/2014	QualityScore4	0.307729653
3/31/2014	QualityScore1	5.422178023
3/31/2014	QualityScore2	0.985466851
3/31/2014	QualityScore3	-2.769785655
3/31/2014	QualityScore4	0.074168688
4/30/2014	QualityScore1	6.320621045
4/30/2014	QualityScore2	1.391671404
4/30/2014	QualityScore3	-2.603796075
4/30/2014	QualityScore4	0.09493454
5/31/2014	QualityScore1	5.971071468
5/31/2014	QualityScore2	1.341159366
5/31/2014	QualityScore3	-2.64347878
5/31/2014	QualityScore4	0.183292807
6/30/2014	QualityScore1	7.453992816
6/30/2014	QualityScore2	1.482939051
6/30/2014	QualityScore3	-3.613017347
6/30/2014	QualityScore4	0.522529344
7/31/2014	QualityScore1	7.588245524
7/31/2014	QualityScore2	1.70721827
7/31/2014	QualityScore3	-3.427167163
7/31/2014	QualityScore4	0.383798662

Any help on how to do this would be greatly appreciated. I can't figure out if it's possible and if i should structure my data different and if it is possible how to set it up in Power BI. Please help

Thanks

[Anonymous]

Hi @Andrewdj ,

Thanks for @saud968 reply.

@Andrewdj Could you please show more details about your requirement? Such as pictures or examples.

According you description, do you want the proportion of the four quality scores in each month to be displayed as a percentage and the percentage sum of each quality score to be 100%?

If so, I recommend you to calculate the sum of the four quality scores for each month, that is, get a different weight for each quality score for each month, and then use division to get the proportion of each quality score.

CurrentMonthPercentage = Table[Value]/CALCULATE(SUM(Table[Value]),FILTER(ALL(Table),MONTH(EARLIER(Table[Date]))=MONTH(Table[Date])))

Best regards,

Mengmeng Li

[saud968]

Calculate the Quality Score
Since the sub-scores have different weights, you’ll need to calculate the overall Quality Score. You can do this by creating a new calculated column in Power BI.

Go to the Data view.
Select your table.
Click on New Column and enter the following DAX formula:
QualityScore =
SUMX(
'YourTable',
SWITCH(
'YourTable'[QualitySubScores],
"QualityScore1", 'YourTable'[Value] * 0.30,
"QualityScore2", 'YourTable'[Value] * 0.30,
"QualityScore3", 'YourTable'[Value] * 0.20,
"QualityScore4", 'YourTable'[Value] * 0.20,
0
)
)

Replace 'YourTable' with the actual name of your table.

Create the Line Chart
Go to the Report view.
Insert a Line Chart.
Drag the Date field to the Axis.
Drag the QualityScore column to the Values.

Enable Drill Down
To enable drill down in the same chart:

Add the QualitySubScores field to the Legend.
Click on the Drill Down button in the top-right corner of the chart (it looks like a downward arrow).

Adjust the Interactions
Ensure that the interactions between the main Quality Score and the sub-scores are set correctly:

Click on the chart to select it.
Go to the Format pane.
Under Data Colors, you can customize the colors for each sub-score to make them distinct.
Step 6: Test the Drill Down
Click on the data points in your line chart to drill down and see the sub-scores. The chart should now display the sub-scores when you drill down, reflecting their individual contributions based on the weights.

Additional Tips
Tooltips: Customize tooltips to show the weighted values when hovering over data points.
Filters: Use slicers or filters to allow users to select specific time periods or sub-scores.

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!