Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi, here is my sample data:
data_quarter | data_month | sales | sales_A | sales_B | cost | cost_A | cost_B | cost_C | margin | margin_A | margin_B | margin_C | margin_% |
Q1 | 2022.OCT | 141,561 | 164,003 | 109,044 | 635,947 | 414,608 | 74,985 | 146,354 | 551,997 | 109,829 | 331,168 | 111,000 | 389.94% |
Q1 | 2022.NOV | 181,679 | 224,267 | 150,175 | 832,144 | 556,121 | 113,022 | 163,001 | 699,471 | 158,824 | 434,847 | 105,800 | 385.00% |
Q1 | 2022.DEC | 102,562 | 126,920 | 86,276 | 474,110 | 315,758 | 65,232 | 93,120 | 394,064 | 87,300 | 245,651 | 61,113 | 384.22% |
Q1 | 2023.Q1 | 79,117 | 97,347 | 63,899 | 358,034 | 240,363 | 47,790 | 69,881 | 305,406 | 71,524 | 189,196 | 44,687 | 386.02% |
Q2 | 2023.JAN | 90.4 | 90.1 | 83.4 | 279 | 83.4 | 98.1 | 97.2 | 312 | 102.3 | 102.3 | 107.0 | 344.61% |
Q2 | 2023.FEB | 14,822 | 10,856 | 12,174 | 35,308 | 12,174 | 14,159 | 8,975 | 28,234 | 9,060 | 9,060 | 10,114 | 190.48% |
Q2 | 2023.MAR | 47,377 | 48,592 | 46,547 | 141,519 | 46,547 | 51,164 | 43,808 | 129,397 | 42,312 | 42,312 | 44,773 | 273.12% |
Q2 | 2023.Q2 | 119,339 | 117,654 | 96,907 | 314,987 | 96,907 | 109,274 | 108,806 | 320,194 | 102,826 | 102,826 | 114,543 | 268.31% |
Q3 | 2023.APR | 181,538 | 177,101 | 155,627 | 491,814 | 155,627 | 174,598 | 161,589 | 477,825 | 154,198 | 154,198 | 169,430 | 263.21% |
Q3 | 2023.MAY | 102,562 | 126,920 | 86,276 | 474,110 | 315,758 | 65,232 | 93,120 | 394,064 | 87,300 | 245,651 | 61,113 | 384.22% |
Q3 | 2023.JUN | 79,117 | 97,347 | 63,899 | 358,034 | 240,363 | 47,790 | 69,881 | 305,406 | 71,524 | 189,196 | 44,687 | 386.02% |
Q3 | 2023.Q3 | 90.4 | 90.1 | 83.4 | 279 | 83.4 | 98.1 | 97.2 | 312 | 102.3 | 102.3 | 107.0 | 344.61% |
Q4 | 2023.JUL | 14,822 | 10,856 | 12,174 | 35,308 | 12,174 | 14,159 | 8,975 | 28,234 | 9,060 | 9,060 | 10,114 | 190.48% |
Q4 | 2023.AUG | 47,377 | 48,592 | 46,547 | 141,519 | 46,547 | 51,164 | 43,808 | 129,397 | 42,312 | 42,312 | 44,773 | 273.12% |
Q4 | 2023.SEP | 119,339 | 117,654 | 96,907 | 314,987 | 96,907 | 109,274 | 108,806 | 320,194 | 102,826 | 102,826 | 114,543 | 268.31% |
Q4 | 2023.Q4 | 181,538 | 177,101 | 155,627 | 491,814 | 155,627 | 174,598 | 161,589 | 477,825 | 154,198 | 154,198 | 169,430 | 263.21% |
I want to create a matrix to show values in rows and these values should displayed in hierarchy, and put the date_quarter and date_month dimensions in columns.
here is what it displayed after simply drap the columns and values and I switched values to rows:
one problem is that I cannot display the values in a hierarchical relationship, here is the expected result:
do you have any solutions? Thank you
Solved! Go to Solution.
In order to analyze the table effectively, it should be tall and open.
The PBI engine cannot work with pivoted tables in a flexible manner
It must be done at the source of the data or in the PQ.
In this case, Dax manipulations will be cumbersome and ineffective.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Hi @Anonymous
To achieve your goal and to analyze your data you need firstly unpivot your table.
Please Follow nex steps :
In PQ
Select 2 first columns and unpivot the others
in the next step Filter your subtotals:
In this stage, you have your measures an dates.
Now let's have a "measure group" column.
You can achieve it easily with "column from example"
than close and apply
Result:
Link to Samle file
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you. actually the real data is more complicated because these are divide calculations so that it is not convenient to transform via PQ, do you have alternative DAX soultions? Thank you.
In order to analyze the table effectively, it should be tall and open.
The PBI engine cannot work with pivoted tables in a flexible manner
It must be done at the source of the data or in the PQ.
In this case, Dax manipulations will be cumbersome and ineffective.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |