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,

[truiz-rico]

Hi I need you help, because I am facing to a similar problem.

I am trying to create a line chart with 

x axis (datetime column). --> A

y axis (average of a column). --> B

secondary y axis (an already measure created). --> C

Then I would like to include legend which only affects y axis (B). This legend is another column belongs to the same table

Then when I am trying to create my line chart and I complete A, B and C, pbix cannot allow me to include legend. Maybe its because should I change some of the A,B, and C?

When I try to create the line chart with no legend, the result is legitime, but it is impossible fullying legend field.

Please could you help me? It is a similar questions to the ones you answer before.

[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