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?
Solved! Go to Solution.
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'
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...
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)
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.
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?