Filtering issue when getting cumulative total with dax

[Anonymous]

Hello community,

I'm trying to prepare a Power Bi report in which I contrast two products with each other. I'm working on contrasting subtarrıtories which made up %80 off sales of both of the products. For that, at first I've made a Measure which is the cumulative total of the two products. Afterwards I'm trying to filter this products with a Measure I've made. But when I filter common subtarrıtories cumulative, The Measure works again. I don't want this filter to effect cumulative Measure. Can you help me with that?

pbix: https://drive.google.com/file/d/1RiYp0mmmVOGPA7Yn1QiDzDMi39fkTAf1/view?usp=sharing

Here's the DAX of the Cumulative Total Measure I'm using:

Cumulative Total Urun1 = CALCULATE(
    SUM( 'Produts 1'[SUM UNITS 1] ),
    FILTER( ALLSELECTED( 'Produts 1' ),
        'Produts 1'[Ranking 1] <= MAX( 'Produts 1'[Ranking 1] )
    ))

SinceI was thinking about adding many different types of filter, I've protected all the filters in Allselecteds Products1 table.

The Dax of the Filter Measure that I used is like this:

İlk80 f = IF(
    HASONEVALUE('%80 Table'[Value]),
    IF('Measure-'[Cumulative Percent 1]>0 || 'Measure-'[Cumulative Percent 2] >0,
    IF(VALUE('%80 Table'[Value Measure])=0, 
    IF('Measure-'[Cumulative Percent 1]>0 || 'Measure-'[Cumulative Percent 2] >0 ,1,BLANK() ),
    IF(VALUE('%80 Table'[Value Measure])=1, 
    IF('Measure-'[Cumulative Percent 1]<0.8 && 'Measure-'[Cumulative Percent 2] <0.8 ,1,BLANK()  ),
    if(VALUE('%80 Table'[Value Measure])=2,
    IF('Measure-'[Cumulative Percent 1]<0.8 && 'Measure-'[Cumulative Percent 2] >0.8 ,1,BLANK()  ),
    if(VALUE('%80 Table'[Value Measure])=3,
    IF('Measure-'[Cumulative Percent 1]>0.8 && 'Measure-'[Cumulative Percent 2] <0.8 ,1,BLANK()  ),
    IF(VALUE('%80 Table'[Value Measure])=4, 
    IF('Measure-'[Cumulative Percent 1]>0.8 && 'Measure-'[Cumulative Percent 2] >0.8 ,1,BLANK()  )
    ))))),BLANK() ))

Regards

[v-chenwuz-msft · Accepted solution]

Hi @Anonymous ,

Your picture is not very clear. Is the second one the result you want?

When you filter, you do not want the cumulative total change?

Please consider to selected show items with no data.

Maybe your dax can change to this. I have changed your measure [80%] in your pbix in order to match 80%table.

İlk80 f =
IF(
    SELECTEDVALUE( '%80 Table'[Condition] ) = "All"
        || ISFILTERED( '%80 Table'[Condition] ) = FALSE(),
    1,
    IF( SELECTEDVALUE( '%80 Table'[Condition] ) = [%80], 1, BLANK() )
)

result:

Pbix in the end you can refer, if i misunderstood you, please let me know.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.