The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now
Hi to everybody!
I have this problem:
I have this data:
Country | Sell | Cost |
Argentina | 3455 | 675 |
USA | 3321 | 346 |
Australia | 6754 | 720 |
Argentina | 4332 | 786 |
Canada | 7753 | 986 |
Australia | 5522 | 432 |
Argentina | 9856 | 936 |
USA | 5436 | 876 |
Spain | 3876 | 946 |
Spain | 7634 | 345 |
USA | 9468 | 234 |
Argentina | 7643 | 745 |
And I need to reshape table to obtain something like that:
Argentina | Australia | Canadá | Spain | USA | |
Sells | 25286 | 12276 | 7753 | 11510 | 18225 |
Costs | 3142 | 1152 | 986 | 1291 | 1456 |
Difference | 22144 | 11124 | 6767 | 10219 | 16769 |
The main idea is tu summarize the rows with few different measures (a lot) and transpose the summarized rows for use it as columns.
I started with the basic SUMMARIZE Formula in DAX:
Sells Country = SUMMARIZE(Table1,Table1[Country],"Sells",SUM(Table1[Sell]))
But I dont know how to continue....
Could somebody help me?
Thanks!!
Solved! Go to Solution.
Hi @rauldip22 - you want to use a matrix visual instead of trying to recreate a table. Country would be the columns, and your different measures for Sell, Cost, and Difference would be the values. You'll also need to turn on "Show on Rows" under "Values" in the format painter.
Hope this helps
David
Hi @rauldip22 - you want to use a matrix visual instead of trying to recreate a table. Country would be the columns, and your different measures for Sell, Cost, and Difference would be the values. You'll also need to turn on "Show on Rows" under "Values" in the format painter.
Hope this helps
David
Thanks!! It worked!
I have another question, there is a way to add aditional columns into the same Matrix? For example, If I Have "days" column like that:
Country | Category | Sells | Cost |
Argentina | A | 3455 | 675 |
USA | A | 3321 | 346 |
Australia | C | 6754 | 720 |
Argentina | B | 4332 | 786 |
Canada | A | 7753 | 986 |
Australia | C | 5522 | 432 |
Argentina | B | 9856 | 936 |
USA | A | 5436 | 876 |
Spain | A | 3876 | 946 |
Spain | B | 7634 | 345 |
USA | C | 9468 | 234 |
Argentina | C | 7643 | 745 |
I would like to add the Category column to the matrix and obtain something like this:
Argentina | Australia | Canadá | Spain | USA | A | B | C | |
Sells | 25286 | 12276 | 7753 | 11510 | 18225 | 23841 | 21822 | 29387 |
Costs | 3142 | 1152 | 986 | 1291 | 1456 | 3829 | 2967 | 2131 |
Difference | 22144 | 11124 | 6767 | 10219 | 16769 | 20012 | 18855 | 27256 |
There is a way to develop this?
I don't think that is possible in a single matrix. You could rig up something where there are two matrix visuals and the one with Country overlaps the one with Category, but as soon as you get more countries or applied a slicer the whole thing would become a mess.
Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.
User | Count |
---|---|
158 | |
109 | |
96 | |
83 | |
75 |
User | Count |
---|---|
154 | |
137 | |
131 | |
81 | |
62 |