Table cells returning total values instead of splitting by category

[ConnorCrawford]

Hi,

I've created the following table using measures; however, when I'm getting to a column that needs to ignore the page filter for period, then filter as per the measure (less or equal to period on the filter), it is bringing back the correct total value YTD, but it's putting the total in each cell rather than splitting as per the service line categories.

The measure I'm using is as follows:

Revenue YTD = calculate([Margin], ALL(), 'Date'[Period] <= SELECTEDVALUE('Date'[Period]), Actuals[PL Type] = "Revenue")

How can I get it to split as per the categories?

[Bibiano_Geraldo]

Hi @ConnorCrawford ,

The issue is that you're removing all filters ALL(), which includes the Service Line category so your calculation ignores that and just shows the total across everything.

Please try to update your DAX to look like this:

Revenue YTD = 
CALCULATE(
    [Margin],
    REMOVEFILTERS('Date'),
    FILTER(
        ALL('Date'),
        'Date'[Period] <= SELECTEDVALUE('Date'[Period])
    ),
    Actuals[PL Type] = "Revenue"
)

Let me know if this help you

[rajendraongole1 · Accepted solution]

hi @ConnorCrawford  -  can you try the below formaule :

Revenue YTD =
CALCULATE(
[Margin],
REMOVEFILTERS('Date'),
FILTER(
ALL('Date'),
'Date'[Period] <= SELECTEDVALUE('Date'[Period])
),
Actuals[PL Type] = "Revenue"
)

[ConnorCrawford]

This worked - thank you!

[GHOUS2010]

To fix this, you need to modify the REVENUE YTD measure to preserve the Service Line filter context while still applying the Date[Period] filter. You can achieve this by using KEEPFILTERS or adjusting the filter context in the CALCULATE function.

Here’s the corrected DAX measure:

REVENUE YTD =
CALCULATE(
[Margin],
KEEPFILTERS(Actuals[PL Type] = "Revenue"),
Date[Period] <= SELECTEDVALUE(Date[Period])
)

Explanation of Changes

• Remove ALLO(): The ALLO() function removes all filters from the table, including the Service Line filter applied by the table's row context. By removing ALLO(), the measure will respect the Service Line filter.
• Add KEEPFILTERS: Wrapping Actuals[PL Type] = "Revenue" in KEEPFILTERS ensures that the existing filter context (e.g., Service Line) is preserved while applying the new filter for PL Type.
• Keep Date[Period] Filter: The Date[Period] <= SELECTEDVALUE(Date[Period]) ensures the page-level filter for the period is still applied, giving you the correct YTD calculation.

Expected Result

After applying this updated measure, the REVENUE YTD column in your table should split the values by Service Line instead of showing the total in every cell. For example:

• For "Car Parking," it will calculate the YTD revenue for only that service line.
• For "Cleaning," it will calculate the YTD revenue for only that service line, and so on.

Additional Steps

1. Update the Measure: In Power BI, go to the Modeling tab, find the REVENUE YTD measure, and replace the DAX with the updated version above.
2. Refresh the Table: Return to your report page and refresh the table to see the updated values.
3. Verify the Results: Check if the REVENUE YTD column now shows different values for each Service Line, matching the expected YTD revenue for each category.

If the Issue Persists

• Check Relationships: Ensure there’s a proper relationship between the Date, Actuals, and any other related tables (e.g., a table containing Service Line). If the relationships are broken, the filter context might not propagate correctly.
• Debug the Measure: Use DAX Studio or create a temporary measure to test the filter context (e.g., Test = CONCATENATEX(ALLSELECTED(Service Line), Service Line[Service Line], ", ")) to see which filters are being applied.
• Check Page Filters: Ensure there are no additional page-level filters interfering with the Service Line column.

This solution should resolve the issue and give you the correct YTD revenue split by Service Line. Let me know if you need further clarification!