Cumulative totals using measure instead of a calculated column

[Vsb79]

Hello all,
I am working on displaying the cumulative totals on a line chart. I do get it correct when I use the calculated columns, but when I do the same with a measure which I would like to use, unless I select the value in the slicer, the totals don’t show as expected. Am I missing something in my DAX measure? If nothing is selected in the slicer, how to show the total cumulative total for all the projects( using measure)?
Attaching the pbix file.
CumulativeTotal-MeasureVsCalculatedColumn.pbix (43.5 KB)

Thank you for the support!.

[Harsh]

Hello @Vsb79,

Thank You for posting your query onto the Forum.

Well, let’s see the reason first why it was not yielding the results as per the expectations.

The reason being is, there was a bit of a contextual problem i.e., in your measure you have applied the “MAX()” function onto the “Project No” as well. So when no selections are made into the slicer, it takes the highest project number into consideration and evaluates the results, in this case since 6000 is the highest project number, it evaluated the results accordingly as per that project number.

So now, since we also need to evaluate the results, when there’re no selections made into the slicer for “Project No” and therefore, we need to ignore the context of “Project No” at that time (In the measure, I’ve commented out that line for your reference purposes). Below is the revised measure alongwith the screenshot of the final results provided for the reference -

Cumulative MonthlyPrice Measure = 
IF (
    HASONEFILTER ( Sheet2[Project No] ),
    IF (
        SUM ( Sheet2[Monthly Price] ) = BLANK (),
        BLANK (),
        CALCULATE (
            SUM ( Sheet2[Monthly Price] ),
            FILTER (
                ALL ( Sheet2 ),
                Sheet2[Project No] = MAX ( Sheet2[Project No] ) &&
                Sheet2[Period Date] <= MAX ( Sheet2[Period Date] )
            )
        )
    ),
    IF (
        SUM ( Sheet2[Monthly Price] ) = BLANK (),
        BLANK (),
        CALCULATE (
            SUM ( Sheet2[Monthly Price] ),
            FILTER (
                ALL ( Sheet2 ),
                //Sheet2[Project No] = MAX( Sheet2[Project No] )
                Sheet2[Period Date] <= MAX ( Sheet2[Period Date] )
            )
        )
    )
)

I’m also attaching the working of the PBIX file for the reference purposes.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

Cumulative Total - Measure v/s Calculated Column - Harsh.pbix (43.9 KB)

[Vsb79]

Thank you Harsh! That worked. Thank you for the detailed explanation. But, Just for my understanding, why will it not be ALLSELECTED instead of ALL in the measure? Could you please help me understand?
ALLSELECTED in my understanding is that it applies any filters from outside. And if there are multiple slicers/filters that are being applied, it will be taking that into consideration.
For example, if I have more than two projects(3 projects in the updated pbix, sorry, I should have included before for better clarity), and if I select two projects, it’s giving the wrong totals.

Where if we select all projects or one project, it comes correctly

Or is there an alternative for HASONEFILTER that can verify if there are multiple values selected from the slicer?

CumulativeTotal-MeasureVsCalculatedColumn.pbix (47.9 KB)

[Harsh]

Hello @Vsb79,

I went as per the scenario which you had provided and therefore used “ALL()” function. You can replace it with “ALLSELECTED()” as per your scenario.

In case of more than one selection, you can replace “HASONEFILTER()” function with the “ISFILTERED()” function.

Thanks and Warm Regards,
Harsh

[Harsh]

Hello @Vsb79,

Write this measure -

Cumulative MonthlyPrice Measure = 
IF (
    SUM ( Sheet2[Monthly Price] ) = BLANK (),
    BLANK (),
    CALCULATE (
        SUM ( Sheet2[Monthly Price] ),
        FILTER (
            ALLSELECTED ( Sheet2 ),
            Sheet2[Period Date] <= MAX ( Sheet2[Period Date] )
        )
    )
)

Thanks and Warm Regards,
Harsh

Cumulative Total - Measure v/s Calculated Column - Harsh v2.pbix (48.2 KB)

[Vsb79]

Thank you Harsh for the quick response, that worked.