Forum Discussion

Oded-Dror's avatar
Oded-Dror
Helper III
4 years ago
Solved

All with one filter

Hi there,
I have Contoso sales data and I want to create a measure that show fix values (sum sales for color Red)
regardless the slicers selection, This measure dynamiclly change but I need fix values for Red
See image below
Thanks,

Oded Dror

 

  • Its seems this one solved the issue

    Total Sales Fix Red =
    Calculate (SUMX ( Sales, Sales[Quantity] * Sales[Net Price] ),
    All(Sales),
    filter(all('Product'[Color] ) ,'Product'[Color] ="Red"))

8 Replies

  • Oded-Dror , Try like

    return //change return

    Calculate (SalesAmit,

    'Product'[Color] ="Red")

     

    or

     

     

    return //change return

    Calculate (SalesAmit,

    filter(all('Product'[Color] ) , 'Product'[Color] ="Red")

     

    if you do not want other dimensions to work use all(sales) also

    • Oded-Dror's avatar
      Oded-Dror
      Helper III

      Amit,
      Thank you for your response but your solution wont solve the issue when I select for example Year slicer = 2020 it show the same values as the other measure Total Sales Red
      Thanks

      Total Sales Fix Red =
      VAR SalesAmt = SUMX(Sales, Sales[Quantity] * Sales[Net Price])
      Return
      Calculate (SalesAmt,
      filter(all('Product'[Color] ) , 'Product'[Color] ="Red")
      )
  • Oded-Dror the underline problem with your dax measure is that you cannot have apply filter when you have value stored in the VAR, in other words, salesamt you have stored in var is not getting filtered for the color ="Red"

     

    SalesAmt Measure = SUMX(Sales, Sales[Quantity] * Sales[Net Price])
    
    Total Sales Fix Red =
    Calculate ([SalesAmt Measure],
    'Product'[Color] ="Red"
    //filter(all('Product'[Color] ) , 'Product'[Color] ="Red")
    )

     

     

    Follow us on LinkedIn and  to our YouTube channel

     

    Learn about conditional formatting at Microsoft Reactor

    My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

     

    Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

    • Oded-Dror's avatar
      Oded-Dror
      Helper III

      Parry,
      Your solution is not working the measure continue to response to the selected slicers

      And it should show the total sales red measure value at all time

      Total Sales Fix Red =
      Calculate (SUMX ( Sales, Sales[Quantity] * Sales[Net Price] ),
      'Product'[Color] ="Red")
    • Oded-Dror's avatar
      Oded-Dror
      Helper III

      Its seems this one solved the issue

      Total Sales Fix Red =
      Calculate (SUMX ( Sales, Sales[Quantity] * Sales[Net Price] ),
      All(Sales),
      filter(all('Product'[Color] ) ,'Product'[Color] ="Red"))
  • Oded-Dror  what do you mean responding to slicer? What slicer you are referring to? Are you slicing on the color?

  • Oded-Dror if you are filtering product color then then remove the filters and then apply the one you need.

     

    Total Sales Fix Red =
    Calculate (SUMX ( Sales, Sales[Quantity] * Sales[Net Price] ),
    REMOVEFILTERS ( 'Product' ),
    'Product'[Color] ="Red")

     

     

    Follow us on LinkedIn and  to our YouTube channel

     

    Learn about conditional formatting at Microsoft Reactor

    My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

     

    Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

     

  • Oded-Dror awesome, my recent measure should have fixed that too, the measure you used, and it has one drawback, if you filter on any other attribute of the product, you will run into the same issue, let's product name or code or whatever, since you are only removing the filter from color, anyhow, it all depends on your use case, how flexible/scalable solution you want to develop. 

     

    In the end, you have what you need that's what matters. Cheers!!

     

     

    Follow us on LinkedIn and  to our YouTube channel

     

    Learn about conditional formatting at Microsoft Reactor

    My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

     

    Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.