cumulative measure yields incorrect values when slicer applied (data source and pbi attached)

[Solariz9]

I have a meansure to caculate cumulative count of records based on the column "DeltaMonth" as below

K_TOTAL = 
VAR MX = MAX(Data[DeltaMonth])
RETURN CALCULATE(
	DISTINCTCOUNT(Data[Number]),
	FILTER(
		ALL(Data[DeltaMonth]),
		Data[DeltaMonth] <= MX
	)
)

It works quite well, yielding cumulative figures

But when I apply the slicer to both "Severity" & "Edition", the cumulative measure doesn't work properly anymore:

Can anyone help to figure this out? The data source can be found in the attached xlsx table.

 The : PBI file 

 The data source

[rajendraongole1]

Hi @Solariz9 - you can use the REMOVEFILTERS function instead of ALL to retain the other slicers, use the below measure

K_TOTAL =
VAR MX = MAX(Data[DeltaMonth])
RETURN
CALCULATE(
DISTINCTCOUNT(Data[Number]),
FILTER(
REMOVEFILTERS(Data[DeltaMonth]),
Data[DeltaMonth] <= MX
)
)

Hope it works

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[Solariz9]

Hi @rajendraongole1 ,

It seems the syntax is wrong if just replace ALL with REMOVEFILTERS. See this screen copy:

[Anonymous]

Hi @Solariz9 ,

I can't open your shared pbix file and got the file corrupted error. I created a sample pbix file(see the attachment) base on shared excel file. You can update the formula of measure [K_TOTAL] as below to get the expected result...

K_TOTAL = 
VAR MX =
    MAX ( Data[DeltaMonth] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Data[Number] ),
        FILTER ( ALLSELECTED ( Data ), Data[DeltaMonth] <= MX ),
        REMOVEFILTERS ( 'Data'[DeltaMonth] )
    )

Best Regards

[Solariz9]

Dear @Anonymous ,

Thanks so much for your solution. It's approaching to the final destination, but still not perfect.

If keep YYY2401 select, it's working pretty well. But if we select 2 Editions or more, you can see all of Editions are mixed together. As below screen copy. Now I have to doubt, there's something wrong in Power BI ...

I uploaded the compressed pbix file as this, please try if you can open it.

[Anonymous · Accepted solution]

Hi @Solariz9 ,

You can update the formula of measure [K_TOTAL] as below:

K_TOTAL = 
VAR MX =
    SELECTEDVALUE ( Data[DeltaMonth] )
VAR _edition =
    SELECTEDVALUE ( 'Data'[Edition] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Data[Number] ),
        FILTER (
            ALLSELECTED ( Data ),
            'Data'[Edition] = _edition
                && Data[DeltaMonth] <= MX
        ),
        REMOVEFILTERS ( 'Data'[DeltaMonth] )
    )

Best Regards

[Solariz9]

hi @Anonymous 

Thank you so much! the issue is now perfectly solved.

Can you please explain more? why Edition has to be put as SELECTEDVALUE? Why there's no need to put Severity as SELECTEDVALUE? Thanks in advance!