cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
arp Frequent Visitor
Frequent Visitor

Display percentage of filtered total in a line chart

 

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 NameYearSalesCountry
A2015 X
A2016 X
A2017 X
B2015 y
B2016 y
B2017 y
C2015 X
C2016 X
C2017 X
D2015 X
D2016 X
D2017 X
E2015 y
E2016 y
E2017 y

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Display percentage of filtered total in a line chart

@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   www.daxformatter.com

7 REPLIES 7
Super User
Super User

Re: Display percentage of filtered total in a line chart

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 Frequent Visitor
Frequent Visitor

Re: Display percentage of filtered total in a line chart

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...

 

Super User
Super User

Re: Display percentage of filtered total in a line chart

@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)

Community Support Team
Community Support Team

Re: Display percentage of filtered total in a line chart

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
arp Frequent Visitor
Frequent Visitor

Re: Display percentage of filtered total in a line chart

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?

Super User
Super User

Re: Display percentage of filtered total in a line chart

@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   www.daxformatter.com

arp Frequent Visitor
Frequent Visitor

Re: Display percentage of filtered total in a line chart

@AlB

 

That's better! Thank you for your help