Complex Need to calculate top box and top 2 box aggregation in line chart.

[zero99]

Hi All, 

I have data as follows:

Price_Expensive	Price_Normal	Price_Cheap	Price_Cheapest
		Somewhat Unlikely	Somewhat Unlikely
			Somewhat Unlikely
		Extremely Unlikely	Extremely Unlikely
Extremely Likely
Extremely Likely	Extremely Likely	Extremely Likely	Extremely Likely
Somewhat Likely
Somewhat Likely	Somewhat Likely
Somewhat Likely	Somewhat Likely	Somewhat Likely	Somewhat Likely
Somewhat Unlikely	Somewhat Likely	Extremely Likely
Somewhat Likely
Extremely Unlikely	Extremely Unlikely	Extremely Unlikely	Extremely Unlikely
Extremely Likely
Neutral
		Somewhat Unlikely	Neutral
Extremely Likely	Extremely Likely	Extremely Likely	Extremely Likely
	Somewhat Unlikely	Neutral	Neutral
		Extremely Unlikely	Neutral
Somewhat Likely
	Somewhat Unlikely	Somewhat Unlikely	Neutral
Somewhat Unlikely	Neutral	Neutral
Neutral
Extremely Likely
Neutral	Somewhat Likely	Somewhat Likely	Extremely Likely
Somewhat Likely	Somewhat Likely
Somewhat Unlikely	Neutral	Extremely Likely
			Somewhat Unlikely
		Somewhat Unlikely	Neutral

I need to create line chart to that show (Y axis) the percentage of extremely likely and aggregation of extremely likely & somewhat likely against (X axis) the 4 different prices [Cheapest, Cheap, Normal, Expensive]. Final result will look like below:

I have attached the raw sample file and test pbix Link to PBIX & Excel . In the file, there is an existing pie chart (result of this closed thread here. and the raw table has been unpivoted to accomodate those chart. Would appreciate some help on this.

[zero99]

Hi @dk_dk 

Thank you for your help. I decided to use COUNTROWS() as you mentioned and they worked well for my case. However, as a result of another unpivot for the line chart, the figure in the pie chart is inflated significantly. I have made a new thread HERE  regarding this

[dk_dk · Accepted solution]

Hi @zero99 

I only worked with the 4 price columns but I think you can replicate the same with the additional columns you have in your data:

1 - Unpivot the 4 Price columns into Attribute - Value pairs:

2 - Group by (you would need to add grouping for your other columns here, I think). You could also in theory skip the grouping and use COUNTROWS() in the DAX below instead of SUM(Count) column.

Create two measures:

ExtremelyLikely = CALCULATE(SUM(Data[Count]),Data[Value]="Extremely Likely")/SUM(Data[Count])

ExtremelySomewhatLikely = CALCULATE(SUM(Data[Count]),Data[Value] IN {"Extremely Likely", "Somewhat Likely"})/SUM(Data[Count])

Format them as %, and then configure your visual:

Additionally, you will want to custom sort the Price categories so the chart is in the right order. You can refer to this thread to do that. 

Hope this helps, let me know if you have any issues!