Forum Discussion
"Pivoting" a table using DAX
Hello, my data currently looks like this.
| Age | Count |
| 23 | 3020 |
| 45 | 4736 |
| 57 | 2987 |
Basically, the client counted buyers by buyer age. I want to show this count in a histogram, with age buckets like ">20", "20-29", etc. To do this, I used a DAX calculated column. The client also wants to see percentages, so I created another DAX calculated column that takes each Count value and divides it by the total Count column sum. The table looks like this now.
| Age | Count | Age Bucket | Percentage |
| 23 | 3020 | 20-29 | 12% |
| 45 | 4736 | 40-49 | 15% |
| 57 | 2987 | 50-59 | 6% |
However, the client wants a table on the dashboard that looks like this:
Metric | 20-29 | 40-49 | 50-59 |
| Count | 3020 | 4736 | 2987 |
| Percentage | 12% | 15% | 6% |
Is there a good way to do this, I am currently exploring using SUMMARIZECOLUMNS.
In the formatting options for a matrix, under 'values' theres a 'show on rows' option. If you turn this on, then put [Age Bucket] in columns and both [Count] and [Percentage] in values, then this should get you the result you want. You may have to make Count and Percentage measures, but these can just be like:
CountMeasure = SUM('YourTableName'[Count])
1 Reply
In the formatting options for a matrix, under 'values' theres a 'show on rows' option. If you turn this on, then put [Age Bucket] in columns and both [Count] and [Percentage] in values, then this should get you the result you want. You may have to make Count and Percentage measures, but these can just be like:
CountMeasure = SUM('YourTableName'[Count])