Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The 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.

Reply
nirvana_moksh
Impactful Individual
Impactful Individual

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 Names201820192020
DEPARTMENTS $  23,000.00 $  21,000.00 $  18,000.00
% GROWTH10%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

 

 

image.png

1 ACCEPTED SOLUTION
v-lili6-msft
Community Support
Community Support

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:

BeforeBeforeAfterAfter

and here is pbix file, please try it.

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

Hi,

 

You may create the measures and change the format of a particular measure from the modelling pane.

v-lili6-msft
Community Support
Community Support

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:

BeforeBeforeAfterAfter

and here is pbix file, please try it.

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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).

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.