Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Kmcdona1
Frequent Visitor

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
)))

 

Kmcdona1_0-1699983990398.png

 

 

2 ACCEPTED SOLUTIONS

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])
Kmcdona1_1-1700083070879.png

 

 
 
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.

Kmcdona1_0-1700082237538.png

 

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.

View solution in original post

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])

vzhangti_0-1700559746254.png

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.

View solution in original post

7 REPLIES 7
v-zhangti
Community Support
Community Support

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])

vzhangti_0-1700122510004.png

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.

 

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.

 

Kmcdona1_0-1700155938282.png

 

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])

vzhangti_0-1700559746254.png

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.

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

SUMMARIZE!!!!!!
Ritaf1983
Super User
Super User

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

Ritaf1983_0-1700015896885.png

 

Ritaf1983_1-1700015979157.png

And use simple sum DAX :

num_Sold_Rita = sum('Sales'[Sales])
Ritaf1983_2-1700016125767.png

 

And slice it as you need 🙂

Ritaf1983_3-1700016158343.png

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

 

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

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.   

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])
Kmcdona1_1-1700083070879.png

 

 
 
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.

Kmcdona1_0-1700082237538.png

 

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.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.