Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Oguzuyank
Regular Visitor

Filtering issue when getting cumulative total with dax

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

 

Oguzuyank_0-1638348480175.png

 

Oguzuyank_1-1638348508779.png

 

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

1 ACCEPTED SOLUTION
v-chenwuz-msft
Community Support
Community Support

Hi @Oguzuyank ,

 

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.

vchenwuzmsft_0-1638760723614.png

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:

vchenwuzmsft_0-1639531677461.png

 

 

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.

 

View solution in original post

1 REPLY 1
v-chenwuz-msft
Community Support
Community Support

Hi @Oguzuyank ,

 

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.

vchenwuzmsft_0-1638760723614.png

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:

vchenwuzmsft_0-1639531677461.png

 

 

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.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors