Measure incorrect total when using filter from Unrelated table.

[Kmcdona1]

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

)))

[Kmcdona1 · Accepted solution]

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]

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]

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 · Accepted solution]

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.

[Kmcdona1]

Thank you this was perfect.  I was going down the rabbit hole last night trying to read up on 

SUMMARIZE!!!!!!

[Ritaf1983]

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]

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 · Accepted solution]

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.