Calculate Percentage on Final Data Presented in a Chart Visualization

[Anonymous]

Hi Everyone,

I have a clustered column chart (below) that shows Revenue (light blue) and Margin (dark blue) over the span of a few years. This data is taken from 10,000 rows along with multiple filters. I am curious how I can calculate the percentage (margin/revenue) between these two final columns and show it on a secondary axis as a line chart in Power BI. 

Any help would be great!

[Anonymous · Accepted solution]

Hi  @Anonymous ,

I created some data:

Here are the steps you can follow：

1. Create measure.

Measure 
SUMX(
FILTER(ALL('Table'),YEAR('Table'[Date])=YEAR(MAX('Table'[Date]))&&'Table'[Group]=MAX('Table'[Group])),[Value])

Measure 2 =
var _revenue=
SUMX(
FILTER(ALL('Table'),YEAR('Table'[Date])=YEAR(MAX('Table'[Date]))&&'Table'[Group]="Revenue"),[Value])
var _margin=
SUMX(
    FILTER(ALL('Table'),YEAR('Table'[Date])=YEAR(MAX('Table'[Date]))&&'Table'[Group]="Margin"),[Value])
return
DIVIDE(_margin,_revenue)

2. Select Line and clustered column chart visual, place [Measure] to Column y-axis, [Measure2] to Line y-axis.

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly