Formatting %ages

DAX DAX Calculations
1

Hi

I have imported a excel spreadsheet that has different metrics (one being % of Revenue), and have added multiple slicers which works perfectly. Only issue is that when I select % of Revenue, it comes out as a decimal number. How can I rectify the below formula?

CY. =

VAR CURRENTITEM = SELECTEDVALUE( ‘Detailed P&L’[Items (Normalized)] )

RETURN
SWITCH( TRUE() ,
CURRENTITEM = “Total Revenues”, CALCULATE( [Subject IS], ‘Subject hotel’[Items (Normalized)] = “Total Revenues”) ,
CALCULATE( [Subject IS] , FILTER(‘Subject hotel’ , ‘Subject hotel’[Items (Normalized)] = CurrentItem)) )

Stuart

2

Hello @Stuart,

Thank you for posting your query on the Forum.

It would be helpful if you can share sample PBIX file of the working.

But you can try out the below measure and check it in your analysis if this is working.

CY. =

VAR CURRENTITEM = SELECTEDVALUE( ‘Detailed P&L’[Items (Normalized)] )

RETURN
SWITCH( TRUE() ,
CURRENTITEM = “Total Revenues”, FORMAT( CALCULATE( [Subject IS], ‘Subject hotel’[Items (Normalized)] = “Total Revenues”) , "0%" ) , 
FORMAT( CALCULATE( [Subject IS] , 
             FILTER(‘Subject hotel’ , ‘Subject hotel’[Items (Normalized)] = CurrentItem ) ) , "0%" ) )

Hoping it helps you with your analysis.

Thanks & Warm Regards,
Harsh

3

Thanks for the response @Harsh. But I think the above will transform all the values to %ages? I only want %ages when on my splicer I select % of Revenue metric, if I select “Actual”, it should still show as a number.

4

Hello @Stuart,

In that case you’ll required to calculate two different set of measures one for the Actuals and other one for the % of Revenue.

I’m attaching the PBIX File of my working where I’ve two different sets of measures so that when I select the % of Revenue I can view it as per the required format.

Hoping you find it useful and helps you to achieve the desired analysis.

Thanks & Warm Regards,
Harsh

Financial Reporting.pbix (810.8 KB)

5

I was hoping not to add additional DAX, but if that’s the only way, will go ahead with that solution. Thanks

6

I managed to get it to work by including an IF function:

CY =
VAR CURRENTITEM = TRIM(SELECTEDVALUE( ‘Summary P&L template’[Income Statement Items] ))

RETURN
SWITCH( TRUE() ,
CURRENTITEM = “Total Revenues”, CALCULATE( [Subject IS], ‘Subject Hotel Summary’[Items (Normalized)] = “Total Revenues”) ,
IF(Metric[Table Data] = “% TORE” , FORMAT(CALCULATE( [Subject IS] , FILTER(‘Subject Hotel Summary’ , ‘Subject Hotel Summary’[Items (Normalized)] = CurrentItem)) , “0.0%”) , CALCULATE( [Subject IS] , FILTER(‘Subject Hotel Summary’ , ‘Subject Hotel Summary’[Items (Normalized)] = CurrentItem))))

7

Hello @Stuart,

Alternatively as mentioned by you this is also an option as I’ve also worked it out by using “SWITCH( TRUE() )” function.

I had calculated 2 different measures because that would provide me a leverage of dragging an individual measure if I want an analysis just based on one of them and can also help me sometimes to avoid confusion.

Have attached the file which contains an alternative option as suggested by you.

Thanks & Warm Regards,
Harsh

Financial Reporting.pbix (812.0 KB)