Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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 |
Solved! Go to Solution.
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:
Before
After
and here is pbix file, please try it.
Best Regards,
Lin
Hi,
You may create the measures and change the format of a particular measure from the modelling pane.
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:
Before
After
and here is pbix file, please try it.
Best Regards,
Lin
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
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).
User | Count |
---|---|
118 | |
66 | |
65 | |
56 | |
50 |
User | Count |
---|---|
181 | |
85 | |
67 | |
61 | |
53 |