Measure Total not working

[jimbob2285]

Hi

I'm hoping someone can help, please.  I have a measure called 'Revenue_SelectedMon_P' in the PBIX file Ledger Transactions  that calculates a value up to a certain user-selected date, but it's not totalling when no projects are selected or more than one project is selected:

Revenue_SelectedMon_P = 
--IF(ISFILTERED('ProjectTransactions'[Project]),
    CALCULATE(
        SUM('ProjectTransactions'[Revenue]),
        FILTER(
            ALLSELECTED('ProjectTransactions'),
            'ProjectTransactions'[Project] = SELECTEDVALUE('ProjectTransactions'[Project]) &&
            'ProjectTransactions'[AccountingDate_ME] = SELECTEDVALUE('Month'[AccountingDate_ME])
        )
    )--,
--    SUM('ProjectTransactions'[Revenue])
--)

as you can see from the code above, I've tried adding an ISFILTERED condition, but commented it out as that's not working either

Can anyone explain what I'm doing wrong please, I'm just getting to grips with Measures and finding them very difficult compared to calculated columns

Cheers

Jim

[DataNinja777 · Accepted solution]

Hi @jimbob2285 ,

The issue with your measure lies in how it handles the filter context for totals and cases where no project or multiple projects are selected. The problem arises because SELECTEDVALUE only works when a single value is selected. If no projects or multiple projects are selected, SELECTEDVALUE returns BLANK, which causes the filter conditions to fail.

To address this, you can modify your measure to account for cases where no specific project is selected or when multiple projects are selected. A better approach is to use ISFILTERED to check if a project filter is applied and adjust the logic accordingly. Here's an updated version of your measure:

Revenue_SelectedMon_P =
IF(
    ISFILTERED('ProjectTransactions'[Project]),
    CALCULATE(
        SUM('ProjectTransactions'[Revenue]),
        FILTER(
            ALLSELECTED('ProjectTransactions'),
            'ProjectTransactions'[Project] = SELECTEDVALUE('ProjectTransactions'[Project]) &&
            'ProjectTransactions'[AccountingDate_ME] = SELECTEDVALUE('Month'[AccountingDate_ME])
        )
    ),
    CALCULATE(
        SUM('ProjectTransactions'[Revenue]),
        FILTER(
            ALLSELECTED('ProjectTransactions'),
            'ProjectTransactions'[AccountingDate_ME] = SELECTEDVALUE('Month'[AccountingDate_ME])
        )
    )
)

This measure works as follows: If a specific project is selected,

the measure calculates the revenue for that project by using SELECTEDVALUE to identify the selected project. It ensures that the filter context includes the selected project and the specific month. If no project or multiple projects are selected, the measure skips the project filter and calculates the total revenue for all projects in the selected month. This way, the measure handles both individual project selections and totals correctly.

The use of ALLSELECTED ensures that slicer and filter contexts applied in the report are respected, while removing row-level filters to properly calculate totals. By structuring the logic with IF(ISFILTERED), the measure explicitly distinguishes between scenarios where a specific project is selected and where no specific selection is made, improving its reliability for both detailed and aggregated views.

If you are still encountering issues, testing intermediate results using a RETURN statement can help identify where the logic needs further adjustment.

Best regards,

[danextian]

Hi @jimbob2285 

The issue lies with this

SELECTEDVALUE('ProjectTransactions'[Project]) 

This will return blank if there are more than one distinct values. At the total level, there is obviously more than one and also why the need to filter the current row by the same current row value? For example you are filtering Highstairs Lane - 28 Plots to Highstairs Lane - 28 Plots which is what this code below does.

'ProjectTransactions'[Project] = SELECTEDVALUE('ProjectTransactions'[Project])

  I would simplify your code as follows:

CALCULATE (
    SUM ( 'ProjectTransactions'[Revenue] ),
    KEEPFILTERS (
        TREATAS (
            VALUES ( 'Month'[AccountingDate_ME] ),
            'ProjectTransactions'[AccountingDate_ME]
        )
    )
)
--TREATAS is used to propagate a filter between non-related tables, IN can be used but generally TREATAS is faster