Forum Discussion

Kmcdona1's avatar
Kmcdona1
Frequent Visitor
2 years ago
Solved

Measure incorrect total when using filter from Unrelated table.

I am at my wits end with this one and I am probably going at this the wrong way, I have gone through countless posts but could not figure this one out.

 

I have a data set that is set up a certain way that I cannot modify.  I wanted to create a measure that will total up my sales which are in separate columns.  I created a separate unrelated table to create a “pivot like” drop down rather then creating 3 separate measures(cars,trucks,suv).  I need the grand total to add up properly when the user selects more than one “vehicle Type” filter.   

 

I have included a sample of what I am trying to achieve.  I feel like this should be simple enough, but I can’t figure out the exact DAX.  Any help would be greatly appreciated.

 

PBIX:   Sales Sample 

 

DAX: 

Num Sold = if(SELECTEDVALUE('Vehicle Type'[Vehicles]) = "Cars", SUM(Sales[Cars]),
if(SELECTEDVALUE('Vehicle Type'[Vehicles]) = "Trucks", SUM(Sales[Trucks]),
if(SELECTEDVALUE('Vehicle Type'[Vehicles]) = "SUV", SUM(Sales[SUV]),
 
[Total Sold] -----I know I need something else here but I just can't figure out what
)))

 

 

 

Need Help
  • Ritaf1983's avatar
    Ritaf1983
    2 years ago

    Hi Kmcdona1 
    Instead of adding a new table, you should unpivot your table with PQ :

     

    And use simple sum DAX :

    num_Sold_Rita = sum('Sales'[Sales])

     

    And slice it as you need 🙂

    PBIX is attached

    More information about unpivot Here:

    https://www.youtube.com/watch?v=ESap6ptV8fI

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

     

  • Kmcdona1's avatar
    Kmcdona1
    2 years ago

    I found the below post on another thread and used the switch method insted of my DAX listed above and it works and is much cleaner then my orginal DAX except I can't figure out how to total those that were selected.

     

    Switch DAX = SWITCH(SELECTEDVALUE('Vehicle Type'[Vehicles]), "Cars",[cars sum], "Trucks",[trucks sum],"SUV",[suv sum])

     

     
     
    POST FOUND

    I don't think you can just use selectedvalue() directly on the current table. Try following:

    1. create a new table with year column, more years if needed.

     

    2. You do not need VAR Year, remove it. And change the return formula:

    Return

    SWITCH(SELECTEDVALUE(NewTable[Year]),
    2019, (A-A)*D,
    2020, (B-A)*E)

     


    Paul Zheng _ Community Support Team
    If this post helps, please Accept it as the solution to help the other members find it more quickly.

  • v-zhangti's avatar
    v-zhangti
    2 years ago

    Hi, Kmcdona1 

     

    You can try the following methods.
    Measure:

    Total Sold = 
Var _table=UNION(SUMMARIZE(Sales,"Product","Cars","Sum",SUM('Sales'[Cars])),
                 SUMMARIZE(Sales,"Product","Suv","Sum",SUM('Sales'[SUV])),
                 SUMMARIZE(Sales,"Product","Trucks","Sum",SUM('Sales'[Trucks])))
Return
SUMX(FILTER(_table,[Product] in VALUES('Vehicle Type'[Vehicles])),[Sum])

    Is this the result you expect? Please see the attached document.

     

    Best Regards,

    Community Support Team _Charlotte

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

7 Replies

  • Ritaf1983's avatar
    Ritaf1983
    Super User
    2 years ago

    Hi Kmcdona1 
    Instead of adding a new table, you should unpivot your table with PQ :

     

    And use simple sum DAX :

    num_Sold_Rita = sum('Sales'[Sales])

     

    And slice it as you need 🙂

    PBIX is attached

    More information about unpivot Here:

    https://www.youtube.com/watch?v=ESap6ptV8fI

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

     

    • Kmcdona1's avatar
      Kmcdona1
      Frequent Visitor
      2 years ago

      Thank you Ritaf1983 that is definitely something that could work if this was my actual model.  Unfortunately the dataset provided to me is too large and has many more atttributes that it would make in nearly impossible.   

      • Kmcdona1's avatar
        Kmcdona1
        Frequent Visitor
        2 years ago

        I found the below post on another thread and used the switch method insted of my DAX listed above and it works and is much cleaner then my orginal DAX except I can't figure out how to total those that were selected.

         

        Switch DAX = SWITCH(SELECTEDVALUE('Vehicle Type'[Vehicles]), "Cars",[cars sum], "Trucks",[trucks sum],"SUV",[suv sum])

         

         
         
        POST FOUND

        I don't think you can just use selectedvalue() directly on the current table. Try following:

        1. create a new table with year column, more years if needed.

         

        2. You do not need VAR Year, remove it. And change the return formula:

        Return

        SWITCH(SELECTEDVALUE(NewTable[Year]),
        2019, (A-A)*D,
        2020, (B-A)*E)

         


        Paul Zheng _ Community Support Team
        If this post helps, please Accept it as the solution to help the other members find it more quickly.

  • v-zhangti's avatar
    v-zhangti
    Community Support
    2 years ago

    Hi, Kmcdona1 

     

    You can try the following methods.
    Measure:

    Switch DAX = 
Var _N1=SWITCH(SELECTEDVALUE('Vehicle Type'[Vehicles]), "Cars",SUM(Sales[Cars]), "Trucks",SUM(Sales[Trucks]),"SUV",SUM(Sales[SUV]))
Return
IF(HASONEVALUE('Vehicle Type'[Vehicles]),_N1,[Total Sold])

    Is this the result you expect? Please see the attached document.

     

    Best Regards,

    Community Support Team _Charlotte

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

     

    • Kmcdona1's avatar
      Kmcdona1
      Frequent Visitor
      2 years ago

      Thanks v-zhangti 

       

      I appreciate your response. I applied your DAX and the problem is the grand total is not correct when you filter. I am beginning to think what I am trying to accomplish is not doable. I just cannot figure out the DAX to add up only fields selected in the grand total. The total never changes from 92 unless you pick only 1 selection.  If I select Cars only then 22 displays in both fields otherwise always the total of all sales regardless of what is chosen.

       

       

      • v-zhangti's avatar
        v-zhangti
        Community Support
        2 years ago

        Hi, Kmcdona1 

         

        You can try the following methods.
        Measure:

        Total Sold = 
Var _table=UNION(SUMMARIZE(Sales,"Product","Cars","Sum",SUM('Sales'[Cars])),
                 SUMMARIZE(Sales,"Product","Suv","Sum",SUM('Sales'[SUV])),
                 SUMMARIZE(Sales,"Product","Trucks","Sum",SUM('Sales'[Trucks])))
Return
SUMX(FILTER(_table,[Product] in VALUES('Vehicle Type'[Vehicles])),[Sum])

        Is this the result you expect? Please see the attached document.

         

        Best Regards,

        Community Support Team _Charlotte

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