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
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:
Solved! Go to 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.
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.
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.
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.
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.
Thank you this was perfect. I was going down the rabbit hole last night trying to read up on
Hi @Kmcdona1
Instead of adding a new table, you should unpivot your table with PQ :
And use simple sum DAX :
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
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.
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
112 | |
95 | |
91 | |
82 | |
69 |
User | Count |
---|---|
161 | |
126 | |
121 | |
109 | |
95 |