Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi there,
Please take a look at the PBIX and this photo and see if you can help me
I have one table name account and I seperate it into two tables but the requirement was if I can merge these two
Tables into one (see bottom page the excel version) - Can I have two seperate totals on same column in one matrix?
I solve it with t-SQL 4 itterations one for billing one for cost one for total cost and one for billing - ToalCost and it worls but I would like to do it in DAX
Thanks,
Ed Dror
Solved! Go to Solution.
hi, @Anonymous
I'm afraid that add an additional total row is not available for now.
And you may try this way to add a new custom row not a "total" row:
Step1:
Use this formula to create a new category dim table.
Dim CatName = UNION(SUMMARIZE(Account,Account[Category],Account[CatName]),ROW("Category",99,"CatName","Total Cost"))
Step2:
Create a relationship with Account table
Step3:
Adjust your formula as below:
Original = VAR Billing = Calculate( Sum('Account'[Amount]) ,Account[Category] = 0) VAR Categories = Calculate( Sum(Account[Amount]) ,Account[Category] <> 0) Return IF(SELECTEDVALUE('Dim CatName'[CatName])="Total Cost",CALCULATE(SUM(Account[Amount]),FILTER(ALLSELECTED(Account),Account[Category] <> 0)), ABS(Billing - Categories))
Result:
By the way, you could use sort by column to sort the column order
https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column
here is pbix file, please try it.
Best Regards,
Lin
hi, @Anonymous
I'm afraid that add an additional total row is not available for now.
And you may try this way to add a new custom row not a "total" row:
Step1:
Use this formula to create a new category dim table.
Dim CatName = UNION(SUMMARIZE(Account,Account[Category],Account[CatName]),ROW("Category",99,"CatName","Total Cost"))
Step2:
Create a relationship with Account table
Step3:
Adjust your formula as below:
Original = VAR Billing = Calculate( Sum('Account'[Amount]) ,Account[Category] = 0) VAR Categories = Calculate( Sum(Account[Amount]) ,Account[Category] <> 0) Return IF(SELECTEDVALUE('Dim CatName'[CatName])="Total Cost",CALCULATE(SUM(Account[Amount]),FILTER(ALLSELECTED(Account),Account[Category] <> 0)), ABS(Billing - Categories))
Result:
By the way, you could use sort by column to sort the column order
https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column
here is pbix file, please try it.
Best Regards,
Lin
Lin,
After testing it with one category as negative amount the expected result don't match
I added another toal cost to the Dim
And then I did
Lin,
It works,
Thank you
Ed Dror
One more think
I got the total for table A by doing this
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
111 | |
93 | |
88 | |
35 | |
35 |
User | Count |
---|---|
154 | |
101 | |
80 | |
63 | |
54 |