comparing total group vs broken down catergory

[MrNoob]

I am trying to compare data, using a combo chart, so an area chart for the total grouped data,  then compared to each category broken down.  Here is a sample from excel trying to recreate the measures to use in DAX.

 Chart Sample

Each line would be the total dataset, on a secondary axis, while the lines are the same dataset broken down into its subgroups.  Anyone know how to work this in DAX?

[v-sihou-msft · Accepted solution]

@MrNoob

According to your description, you want to make your lines ingore subgroup filtering. Right?

In your scenario, is the sub group on Legend? If so, you can create a measure and limit the filter context into entire table. 

Measure = CALCULATE(SUM(Table[Value]),ALL(Table))

Or if you need to keep the subgroup field filtering and ingore all other fields filtering, you can use ALLEXCEPT()

Measure = CALCULATE(SUM(Table[Value]),ALLEXCEPT(Table,Table[SubGroup]))

You can enable secondary axis in combo chart. 

However, currently there's no Area+Line chart in Power BI Desktop, you may try other combo chart like Column + Line chart. 

Regards,

[P3Tom]

The built-in visualizations for combo charts are (1) the line and stacked column chart and (2) the line and clustered column chart.

Here is a link for a possible solution with two vertical axes.

Tom

www.powerpivotpro.com