Building a cumulative line graph with 2 X-axis columns

[Srikar_Sai]

I am trying to build a cumulative actuals vs planned graph. I have calculated the y axis measures using Dax. The problem is I have 2 axis values, as my data is in such a way that if it was planned for WD 01, Actuals is WD 02, I am getting a disparity. For example if there are 100 planned tasks in WD 01, there are only 80 actuals in WD 01. I am trying to combine both. I had originally thought of a new table with a unified column for WD, but realized due to filter context, my line graph does not interact with the rest of my report. The limitations are

1) I cannot use power query as my WD planned and WD Actuals are calculated columns

2) Using a new table breaks filter context as filters are not flowing from my main table.

3) To create a unified X-axis using Dax, x-axis in Power BI visuals do not accept.

How to solve for the above?

[v-echaithra]

Hi @Srikar_Sai ,

We’d like to follow up regarding the recent concern. Kindly confirm whether the issue has been resolved, or if further assistance is still required. We are available to support you and are committed to helping you reach a resolution.

Best Regards,
Chaithra E.

[Kedar_Pande]

Create a bridge table with all WD values from both planned and actuals. Use this as your X-axis.

Then create measures that ignore the visual's date context:

Cumulative Planned =
CALCULATE(
[Planned Measure],
ALL('Main Table'),
'Main Table'[WD Planned] <= MAX('Bridge Table'[WD])
)

Cumulative Actuals =
CALCULATE(
[Actuals Measure],
ALL('Main Table'),
'Main Table'[WD Actuals] <= MAX('Bridge Table'[WD])
)

Use these measures with the bridge table WD column on your X-axis. This maintains filter context while providing unified dates.

[Srikar_Sai]

Hi Kedar, thank you for the solution. I have tried it but due to filter context my cumulative line graph is not interacting with the rest of my report as my report is built from my main table. Is there a step that I am missing? If not can you let me know how to get the line graph to interact with the rest of my report?

[v-echaithra]

Hi @Srikar_Sai ,

Thank you for reaching out to Microsoft Community.

Create a table that combines both Planned and Actuals into a unified "Work Day" column. Add a new calculated column to specify whether the entry is for Planned or Actual data and use this new table as the axis in the chart.

Unified_WorkDay_Table =

UNION ( SELECTCOLUMNS(PlannedTable, "WD", PlannedTable[WD], "Value", PlannedTable[Value], "Type", "Planned"), SELECTCOLUMNS(ActualTable, "WD", ActualTable[WD], "Value", ActualTable[Value], "Type", "Actual"))

This table now has a WD column, a Value column for both Planned and Actuals, and a Type column to distinguish between them. Now, use this Unified_WorkDay_Table as your X-axis.

Build the cumulative sum in the visual using Value from this new table and differentiate between the Actuals and Planned by Type.

Hope this helps.
Thank you.

[v-echaithra]

Hi @Srikar_Sai ,

Create a Unified Bridge Table

Unified_WorkDay_Table =
UNION (SELECTCOLUMNS(PlannedTable, "WD", PlannedTable[WD], "Value", PlannedTable[Value], "Type", "Planned"),
SELECTCOLUMNS(ActualTable, "WD", ActualTable[WD], "Value", ActualTable[Value], "Type", "Actual"))

Ensure the Filter Context is Maintained, to maintain the filter context from your main table, use USERELATIONSHIP in your measure to force Power BI to respect the relationship with your main data table.

Cumulative Planned =
CALCULATE([Planned Measure],
USERELATIONSHIP('MainTable'[WD], 'Unified_WorkDay_Table'[WD]),
FILTER(
ALL('Unified_WorkDay_Table'),
'Unified_WorkDay_Table'[WD] <= MAX('Unified_WorkDay_Table'[WD])
))

Cumulative Actuals =
CALCULATE([Actuals Measure],
USERELATIONSHIP('MainTable'[WD], 'Unified_WorkDay_Table'[WD]),
FILTER(
ALL('Unified_WorkDay_Table'),
'Unified_WorkDay_Table'[WD] <= MAX('Unified_WorkDay_Table'[WD])
))

Place Unified_WorkDay_Table[WD] as the X-axis in your line graph. Use the Cumulative Planned and Cumulative Actuals measures as the Y-axis.

Thank you.

[Srikar_Sai]

Hi Chaithra, thank you for the solution. I have tried it but due to filter context my cumulative line graph is not interacting with the rest of my report as my report is built from my main table. Is there a step that I am missing? If not can you let me know how to get the line graph to interact with the rest of my report?