Matrix with different format for values

[nirvana_moksh]

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

[Anonymous]

Hi,

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

[v-lili6-msft · Accepted 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:

BeforeAfter

and here is pbix file, please try it.

Best Regards,

Lin

[corange]

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 

[sulovsky]

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