Forum Discussion

hamoso's avatar
hamoso
New Member
2 years ago
Solved

Create aggregate (AVG, MIN, MAX, SUM, ...) Slicer

Hi guys,

 

I couldn't find the answer using the search functionality. Therefore I wanted to ask you guys if you could help me with this case.

 

I have created a matrix table which shows the average occupation levels for different vehicles.

 

I would now like to create a slicer to select whether the minimum, maximum, average or totalized occupation levels are displayed. You can do this by right-clicking in the values field (see picture below), but I would like to have a slicer in the dashboard so that a customer can also filter between the aggregates, for example.

 

 

Does anyone have a solution for this? I would be grateful for any suggestions.

 

Yours faithfully

Homoso

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi hamoso ,

    The Table data is shown below:

    Please follow these steps:

    1.Create a table and enter the following data manually.

    2.Use the following DAX expression to create a measure

     

    Measure = 
VAR _a = SELECTEDVALUE('Table 2'[Type])
VAR _b = IF(_a = "Sum",SUM('Table'[Value]),
            IF(_a = "Average",AVERAGE('Table'[Value]),
                IF(_a = "Minimum",MIN('Table'[Value]),
                    IF(_a = "Maximum",MAX('Table'[Value]),
                        IF(_a = "Count",COUNT('Table'[Value]))))))
RETURN _b

     

    3.Final output

     

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

5 Replies

  • Wilson_'s avatar
    Wilson_
    Icon for Memorable Member rankMemorable Member
    2 years ago

    Hi hamoso,

     

    Look into calculation groups. Here is a potential resource for you; they talk about using an external tool but calculation groups were recently introduced in Power BI as a native functionality I believe.


    ----------------------------------
    If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

     

    P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.

    • hamoso's avatar
      hamoso
      New Member
      2 years ago

      Hi Wilson_ ,

       

      thank you for your suggestion.  Anonymous 's solution was easier to implement, so I opted for this one. Nevertheless, thanks again for your suggestion.

      • Wilson_'s avatar
        Wilson_
        Icon for Memorable Member rankMemorable Member
        2 years ago

        hamoso,

         

        No worries, calculation groups are more robust if you're looking to scale the ability to do what you're trying to do. Understandable to go a different direction if you're just trying to do it one time. Glad you got something that works for you. 😄

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi hamoso ,

    The Table data is shown below:

    Please follow these steps:

    1.Create a table and enter the following data manually.

    2.Use the following DAX expression to create a measure

     

    Measure = 
VAR _a = SELECTEDVALUE('Table 2'[Type])
VAR _b = IF(_a = "Sum",SUM('Table'[Value]),
            IF(_a = "Average",AVERAGE('Table'[Value]),
                IF(_a = "Minimum",MIN('Table'[Value]),
                    IF(_a = "Maximum",MAX('Table'[Value]),
                        IF(_a = "Count",COUNT('Table'[Value]))))))
RETURN _b

     

    3.Final output

     

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

    • hamoso's avatar
      hamoso
      New Member
      2 years ago

      Hi Anonymous ,

       

      you are the best! Thank you for your help. This was excactly what I was looking for. 🙂