Forum Discussion
Matrix with different format for values
Hello All,
I am running into an issue wherein I am trying to create the below visual which is a matrix. In regards to the data, I have manipulated it by unpivoting the data and getting all 'Column Names' as listed below as Attributes, and all of their corresponding values in one single column of 'Values'. The problem of this is that I cannot format the % values as % and the $ values as $ or any other formatting as it all comes from 1 column now. Is there a better way to achieve this? I have tried unpivoting via Power Query and DAX, but this is so frustrating now
| Column Names | 2018 | 2019 | 2020 |
| DEPARTMENTS | $ 23,000.00 | $ 21,000.00 | $ 18,000.00 |
| % GROWTH | 10% | 5% | 1.50% |
| SUM | $ 5,000.00 | $ 98,000.00 | $ 8,798.00 |
| PROFIT | $ 2,100.00 | $ 1,400.00 | $ 900.00 |
| LOSS | $ 300.00 | $ 200.00 | $ 100.00 |
| Earnings Before Tax | $ 150.00 | $ 120.00 | $ 110.00 |
HI, nirvana_moksh
You could use FORMAT Function to custom format it
Measure = IF(SELECTEDVALUE('Table'[Attribute])="%GROWTH", FORMAT(CALCULATE(SUM('Table'[Value])),"Percent"),FORMAT(CALCULATE(SUM('Table'[Value])),"Currency"))Result:
BeforeAfter
and here is pbix file, please try it.
Best Regards,
Lin
4 Replies
HI, nirvana_moksh
You could use FORMAT Function to custom format it
Measure = IF(SELECTEDVALUE('Table'[Attribute])="%GROWTH", FORMAT(CALCULATE(SUM('Table'[Value])),"Percent"),FORMAT(CALCULATE(SUM('Table'[Value])),"Currency"))Result:
BeforeAfter
and here is pbix file, please try it.
Best Regards,
Lin
Hi,
being in the same problem, I have noticed, that in this solution the % fall in grand total (understandably). any intresting trick to avoid it (except for hidint total).
Hi,
This works well. My table is set up with Client Name at the row level and then on the column level you have attribute into two categories (revenue / job count) and further broken down into Month, Period and Date when you drill down in the matrix visual.
I have used your formula to format accordingly value for Revenue in Currency. However, when I use the measure, it will show me all the date I have in the calendar. It is like it doesnt respond to any slicer I have got one anymore.
Any idea how to fix this?
Thank you
