Measure incorrect total when using filter from Unrelated table.

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

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

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

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.