Display percentage of filtered total in a line chart

[arp]

Hi,

I'm trying to display the % of total sales for each groups name over a three year period but only where the country = X.

I can do this in a matrix using the percentage of column total function but am unable to display this in a line chart. Any suggestions how I might do this?

Group Name	Year	Sales	Country
A	2015		X
A	2016		X
A	2017		X
B	2015		y
B	2016		y
B	2017		y
C	2015		X
C	2016		X
C	2017		X
D	2015		X
D	2016		X
D	2017		X
E	2015		y
E	2016		y
E	2017		y

Thanks

[AlB]

Hi @arp

I think you can do the same in a line chart. Place the [Group Name] in the x-axis and Sales in values and choose Show as Percentage.

Set [Country in a slicer to restrict the calculation to 'X'  

[arp]

Hi,

This hasn't worked for me. The group names have filtered to those in country X but the percentage values are still being calculating on total sales rather than just sales in country X...

[v-piga-msft]

Hi @arp,

By my tests, I'm afraid the option Percent of column total is only for matrix visual currently in Power BI as it has Column Field.

For other charts, it only has Percent of grand total. 

For your requirement, you'd better create a measure like AlB suggest to achieve that.

Best  Regards,

Cherry

[AlB]

@arp

OK, then let's try with a measure in the values of the chart:

Measure =
DIVIDE (
    SUM ( Table[Sales] ),
    CALCULATE ( Table[Sales], ALL ( Table1[Group Name] ) )
)

and still with [Country] in the slicer to select "X" (or any other)

[arp]

Thanks both for the replies.

I'm trying to set up the measure as suggested but I'm getting an error message saying that a single value for the sales column in the relevant table cannot be determined?

[AlB · Accepted solution]

@arp

Sorry. I seem to have made a mistake in my previous post. Try this:

Measure =
DIVIDE (
    SUM ( Table[Sales] ),
    CALCULATE ( SUM(Table[Sales]), ALL ( Table1[Group Name] ) )
)

Code formatted with  

[arp]

@AlB

That's better! Thank you for your help