Forum Discussion
Converting measure values in column header in matrix
I have a measure that returns me values like Super, Flop, and Average. I want to use these values as a column name in a matrix and distribute my distinct count of products within these headings e:g
My Table
| Measure | Total Sales |
| Super | 12000 |
| Super | 15000 |
| Flop | 1000 |
| Average | 9000 |
I have calulated that super , flop based on some conditions if my sales >=12000 then it is super , if sales<6000 then flop, and if sales<10000 and sales>6000 then average.Now my expected out put is
| Products | Super | Flop | Average |
Soap | 99 | 66 | 100 |
| TV | 100 | 66 | 55 |
| apple | 121 | 12 | 100 |
| Mobile | 300 | 20 | 200 |
| Watch | 400 | 50 | 100 |
Please help if you have any solutions for this.
Thanks In Advance!
amitchandak Ahmedx Ritaf1983
Hi, Anonymous
Based on the information you have provided, Here are my answers to your questions.
- You need to create a table.
- You can create a Colum:
Column = IF('Table'[Sales] >= 12000, "Super",
IF('Table'[Sales]<6000,"Flop",
IF('Table'[Sales]> 6000 &&'Table'[Sales]<12000,"Average")
)
)
- You can create another column for counting:
Count =
CALCULATE (
COUNT ( 'Table'[Products] ),
FILTER (
'Table',
'Table'[Products]= EARLIER('Table'[Products])&&'Table'[Column] = EARLIER('Table'[Column])
)
)
- Finally you will see the image shown below.
How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Yilong Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
- Fowmy
Super User
Anonymous
You cannot use meaures as row or column headers in visuals. The appraoch you can take is either create a disconnected table with these values Super, Flop, and Average and keep it on the column section of the visual then create a measure to identify the value and calculate your sales amount. The other way is to use Calculation group. Hi, Anonymous
Based on the information you have provided, Here are my answers to your questions.
- You need to create a table.
- You can create a Colum:
Column = IF('Table'[Sales] >= 12000, "Super",
IF('Table'[Sales]<6000,"Flop",
IF('Table'[Sales]> 6000 &&'Table'[Sales]<12000,"Average")
)
)
- You can create another column for counting:
Count =
CALCULATE (
COUNT ( 'Table'[Products] ),
FILTER (
'Table',
'Table'[Products]= EARLIER('Table'[Products])&&'Table'[Column] = EARLIER('Table'[Column])
)
)
- Finally you will see the image shown below.
How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Yilong Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
