Data Formatting in Financial Reporting

[lulumoon]

Hello, I am working on the financial reporting project and just left with one last obstacle to tackle.

With financial reporting template, I also had to create two different scenarios columns which are independently controlled by two different slicers (date and scenario slicers). The DAX code in order to do that is as follows and this works well with what I desired:

However, the problem with bringing in two different scenarios is that I can’t format variables differently - note below that I have to comment out the Gross Profit % and Net Profit %. If not, the whole table would break.

As a result, unfortunately, the Gross Profit % and Net Profit % is now showing up as numbers instead of %.

Is there a way I could fix this issue so that my Gross Profit and Net Profit could show up appropriately as a percent instead of numbers?

Apologize if my explanation isn’t clear but I hope it makes sense. I have also attached the project here for convenience.

Financial Reporting test.pbix (1007.4 KB)

[Harsh]

Hello @lulumoon,

Thank You for posting your query onto the Forum.

Well the similar type of queries had been asked onto the Forum where members had tried to format the percentages (%) as well as the numbers within the same measure. But truly speaking as of today, there’s no way around of it. Power BI doesn’t allow to format two different variables or types within the same measure.

Sam had also agreed in one of his “Learning Summit Series” videos of Financial Reporting where a live query was asked by a member and he said that there’s no option. The only thing that you can use is “FORMAT()” to format the percentage % figures. And he also said that once you use this function the figures gets converted into the Text.

I’m providing a link below of the queries asked by the members onto the forum and the solutions provided to them.

I’m attaching the working of my PBIX file on Financial Reporting for the reference. (If it helps any).

Hoping you find this useful.

Thanks and Warm Regards,
Harsh

Income Statement and Balance Sheet - Harsh.pbix (859.9 KB)

Formatting %ages

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( [S…

Switch axis between numeric and %

Hello @satichun, Still it wouldn’t be possible because when you use the "SWITCH( TRUE () )" logic the percentage number will still show it as 0.00 Format on Y-axis instead of 0.00% although you may have 2 different measures. I would like to take an example to explain it to you on the basis of screenshot provided below - [Switch Format on Y-Axis Based on Slicer Selected - 1] [Switch Format on Y-Axis Based on Slicer Selected] In the given example, I’ve two different measures one is “Total S…

[lulumoon]

Hi Harsh,

Thank you for the reply! I understood about the FORMAT() function and it would convert it to text and I am okay with that.

However, my problem is after I bring in two independent slicers (date and scenario) using TREATAS function below, the FORMAT function would mess up my entire table.

Thus I had to comment out that line of code in order to make it work.

Sorry for making it difficult but would want to make sure I am posing my question clear.

[Harsh]

Hello @lulumoon,

Is this the results that you’re trying to achieve? Below is the screenshot of the result provided for the reference -

The first table represents your answer and the second table represents my solution.

Now, I would like to list some of the problems that I’ve witnessed in your PBIX file -

Point No. 1: There’s an problem in your “Selected Year NP Formula”. As I’d said in my previous post that once you convert your figures using the “FORMAT()” function they gets converted into the “Text” format. Since your primary measure was converted into the “Text” format. The branched out measures yielded the same format i.e. “NP Senario A” and “NP Senario B” measures. And you tried to do “Subtraction” between the figures which were in “Text” format.

Point No. 2: Why one master measure and two branched out measures for scenario? You can simply create one measure for “Scenario A” and one measure for “Scenario B”. There was absolutely no need to create a master measure and then branch out in the form of two scenarios. In my case, I’ve created one measure for “Scenario A” and one for “Scenario B”. Below are both the formulas provided for the reference -

Scenario A - Harsh = 
CALCULATE(
VAR CURRENTITEM = SELECTEDVALUE ('Financial_Template'[Normalized Account]) 

VAR SelectedYearValues = 
SWITCH (TRUE (),
CURRENTITEM = "Total Revenues", DIVIDE ( [Revenues NP],1000,0), 


CURRENTITEM = "Total COGS", DIVIDE ([COS_NP],1000,0), 
CURRENTITEM = "Total Operating Expense", DIVIDE ( [Total Operating Expense NP],1000,0), 
CURRENTITEM = "Total OIBT", DIVIDE ( [Total OIBT NP],1000,0), 
CURRENTITEM = "Total Gross Profit" , DIVIDE ( [Gross Profit NP],1000,0),
CURRENTITEM = "Gross Profit %" , FORMAT( [GP Margin NP], "0.00%") , 
CURRENTITEM = "R&D Opex" , DIVIDE ( [Total R&D NP],1000,0), 
CURRENTITEM = "Sales Opex" , DIVIDE ( [Total Sales NP],1000,0), 
CURRENTITEM = "Marketing Opex" , DIVIDE ( [Total Marketing NP],1000,0), 
CURRENTITEM = "G&A Opex" , DIVIDE ( [Total G&A NP],1000,0), 
CURRENTITEM = "Restructuring/Amortization" , DIVIDE ( [Restructuring/Amortization NP],1000,0),
CURRENTITEM = "Total Operating Expense" , DIVIDE ( [Total Operating Expense NP],1000,0),
CURRENTITEM = "Non-Op", DIVIDE ( [Non-Op NP],1000,0), 
CURRENTITEM = "Transaction Gain/(Loss)", DIVIDE ( [Transaction Gain/(Loss) NP],1000,0), 
CURRENTITEM = "PBT", DIVIDE ( [PBT NP],1000,0), 
CURRENTITEM = "Income Tax Benefit/(Expense)", DIVIDE ( [Income Tax Benefit/(Expense) NP],1000,0), 
CURRENTITEM = "Net Income / (Loss)", DIVIDE ( [Net Income/(Loss) NP],1000,0),
CURRENTITEM = "Net Profit %" , FORMAT( [Net Income% NP], "0.00%") , 

      BLANK () )

RETURN
SelectedYearValues , 
    ALL( Dates ),
    ALL( Scenario ),
    TREATAS( VALUES( 'Dates-A'[Dates-A] ), Dates[Dates] ),
    TREATAS( VALUES( 'Scenario-A'[Scenario-A] ), Scenario[Scenario] ) )




Scenario B - Harsh = 
CALCULATE(
VAR CURRENTITEM = SELECTEDVALUE ('Financial_Template'[Normalized Account]) 

VAR SelectedYearValues = 
SWITCH (TRUE (),
CURRENTITEM = "Total Revenues", DIVIDE ( [Revenues NP],1000,0), 


CURRENTITEM = "Total COGS", DIVIDE ([COS_NP],1000,0), 
CURRENTITEM = "Total Operating Expense", DIVIDE ( [Total Operating Expense NP],1000,0), 
CURRENTITEM = "Total OIBT", DIVIDE ( [Total OIBT NP],1000,0), 
CURRENTITEM = "Total Gross Profit" , DIVIDE ( [Gross Profit NP],1000,0),
CURRENTITEM = "Gross Profit %" , FORMAT( [GP Margin NP], "0.00%") , 
CURRENTITEM = "R&D Opex" , DIVIDE ( [Total R&D NP],1000,0), 
CURRENTITEM = "Sales Opex" , DIVIDE ( [Total Sales NP],1000,0), 
CURRENTITEM = "Marketing Opex" , DIVIDE ( [Total Marketing NP],1000,0), 
CURRENTITEM = "G&A Opex" , DIVIDE ( [Total G&A NP],1000,0), 
CURRENTITEM = "Restructuring/Amortization" , DIVIDE ( [Restructuring/Amortization NP],1000,0),
CURRENTITEM = "Total Operating Expense" , DIVIDE ( [Total Operating Expense NP],1000,0),
CURRENTITEM = "Non-Op", DIVIDE ( [Non-Op NP],1000,0), 
CURRENTITEM = "Transaction Gain/(Loss)", DIVIDE ( [Transaction Gain/(Loss) NP],1000,0), 
CURRENTITEM = "PBT", DIVIDE ( [PBT NP],1000,0), 
CURRENTITEM = "Income Tax Benefit/(Expense)", DIVIDE ( [Income Tax Benefit/(Expense) NP],1000,0), 
CURRENTITEM = "Net Income / (Loss)", DIVIDE ( [Net Income/(Loss) NP],1000,0),
CURRENTITEM = "Net Profit %" , FORMAT( [Net Income% NP], "0.00%") , 

      BLANK () )

RETURN
SelectedYearValues , 
    ALL( Dates ),
    ALL( Scenario ),
    TREATAS( VALUES( 'Dates-B'[Dates-B] ), Dates[Dates] ),
    TREATAS( VALUES( 'Scenario-B'[Scenario-B] ), Scenario[Scenario] ) )

Point No. 3: Now, since “Scenario A” and “Scenario B” measures contains the “FORMAT()” function I cannot subtract both the measures i.e. Measure Branching technique is not possible in this case. So wrote another measure to calculate the Difference figures. And I excluded the “Gross Profit %” and “Net Profit %” since they’re not the figures in term of Amount (They’re in terms of Percentages [%] ). Below is the measure provided for the reference to calculate the Difference figures -

Scenario Difference - Harsh = 
VAR ScenarioA = 
CALCULATE(
VAR CURRENTITEM = SELECTEDVALUE ('Financial_Template'[Normalized Account]) 

VAR SelectedYearValues = 
SWITCH (TRUE (),
CURRENTITEM = "Total Revenues", DIVIDE ( [Revenues NP],1000,0), 


CURRENTITEM = "Total COGS", DIVIDE ([COS_NP],1000,0), 
CURRENTITEM = "Total Operating Expense", DIVIDE ( [Total Operating Expense NP],1000,0), 
CURRENTITEM = "Total OIBT", DIVIDE ( [Total OIBT NP],1000,0), 
CURRENTITEM = "Total Gross Profit" , DIVIDE ( [Gross Profit NP],1000,0),
CURRENTITEM = "R&D Opex" , DIVIDE ( [Total R&D NP],1000,0), 
CURRENTITEM = "Sales Opex" , DIVIDE ( [Total Sales NP],1000,0), 
CURRENTITEM = "Marketing Opex" , DIVIDE ( [Total Marketing NP],1000,0), 
CURRENTITEM = "G&A Opex" , DIVIDE ( [Total G&A NP],1000,0), 
CURRENTITEM = "Restructuring/Amortization" , DIVIDE ( [Restructuring/Amortization NP],1000,0),
CURRENTITEM = "Total Operating Expense" , DIVIDE ( [Total Operating Expense NP],1000,0),
CURRENTITEM = "Non-Op", DIVIDE ( [Non-Op NP],1000,0), 
CURRENTITEM = "Transaction Gain/(Loss)", DIVIDE ( [Transaction Gain/(Loss) NP],1000,0), 
CURRENTITEM = "PBT", DIVIDE ( [PBT NP],1000,0), 
CURRENTITEM = "Income Tax Benefit/(Expense)", DIVIDE ( [Income Tax Benefit/(Expense) NP],1000,0), 
CURRENTITEM = "Net Income / (Loss)", DIVIDE ( [Net Income/(Loss) NP],1000,0),

      BLANK () )

RETURN
SelectedYearValues , 
    ALL( Dates ),
    ALL( Scenario ),
    TREATAS( VALUES( 'Dates-A'[Dates-A] ), Dates[Dates] ),
    TREATAS( VALUES( 'Scenario-A'[Scenario-A] ), Scenario[Scenario] ) )

VAR ScenarioB = 
CALCULATE(
VAR CURRENTITEM = SELECTEDVALUE ('Financial_Template'[Normalized Account]) 

VAR SelectedYearValues = 
SWITCH (TRUE (),
CURRENTITEM = "Total Revenues", DIVIDE ( [Revenues NP],1000,0), 


CURRENTITEM = "Total COGS", DIVIDE ([COS_NP],1000,0), 
CURRENTITEM = "Total Operating Expense", DIVIDE ( [Total Operating Expense NP],1000,0), 
CURRENTITEM = "Total OIBT", DIVIDE ( [Total OIBT NP],1000,0), 
CURRENTITEM = "Total Gross Profit" , DIVIDE ( [Gross Profit NP],1000,0),
CURRENTITEM = "R&D Opex" , DIVIDE ( [Total R&D NP],1000,0), 
CURRENTITEM = "Sales Opex" , DIVIDE ( [Total Sales NP],1000,0), 
CURRENTITEM = "Marketing Opex" , DIVIDE ( [Total Marketing NP],1000,0), 
CURRENTITEM = "G&A Opex" , DIVIDE ( [Total G&A NP],1000,0), 
CURRENTITEM = "Restructuring/Amortization" , DIVIDE ( [Restructuring/Amortization NP],1000,0),
CURRENTITEM = "Total Operating Expense" , DIVIDE ( [Total Operating Expense NP],1000,0),
CURRENTITEM = "Non-Op", DIVIDE ( [Non-Op NP],1000,0), 
CURRENTITEM = "Transaction Gain/(Loss)", DIVIDE ( [Transaction Gain/(Loss) NP],1000,0), 
CURRENTITEM = "PBT", DIVIDE ( [PBT NP],1000,0), 
CURRENTITEM = "Income Tax Benefit/(Expense)", DIVIDE ( [Income Tax Benefit/(Expense) NP],1000,0), 
CURRENTITEM = "Net Income / (Loss)", DIVIDE ( [Net Income/(Loss) NP],1000,0),

      BLANK () )

RETURN
SelectedYearValues , 
    ALL( Dates ),
    ALL( Scenario ),
    TREATAS( VALUES( 'Dates-B'[Dates-B] ), Dates[Dates] ),
    TREATAS( VALUES( 'Scenario-B'[Scenario-B] ), Scenario[Scenario] ) )

RETURN
ScenarioA - ScenarioB

Point No. 4: Now, to calcuate the Percentage (%) figures. I wrote almost the same measure as mentioned in the Point No. 3. But now I’ve included the “Gross Profit %” and “Net Profit %” measures into the formula.

The most important point to be noted over here is that -

I’ve not used the “FORMAT()” function at all.

1. For “Gross Profit %”, I’ve used the [GP Margin NP] measure and
2. For “Net Profit %” I’ve used the [Net Income% NP].

As simple as that. Because I don’t want them to be converted into the “Text” format. Below is the last and final formula provided for the reference -

Scenario Change - Harsh = 
VAR ScenarioA = 
CALCULATE(
VAR CURRENTITEM = SELECTEDVALUE ('Financial_Template'[Normalized Account]) 

VAR SelectedYearValues = 
SWITCH (TRUE (),
CURRENTITEM = "Total Revenues", DIVIDE ( [Revenues NP],1000,0), 


CURRENTITEM = "Total COGS", DIVIDE ([COS_NP],1000,0), 
CURRENTITEM = "Total Operating Expense", DIVIDE ( [Total Operating Expense NP],1000,0), 
CURRENTITEM = "Total OIBT", DIVIDE ( [Total OIBT NP],1000,0), 
CURRENTITEM = "Total Gross Profit" , DIVIDE ( [Gross Profit NP],1000,0),
CURRENTITEM = "Gross Profit %" , [GP Margin NP] , 
CURRENTITEM = "R&D Opex" , DIVIDE ( [Total R&D NP],1000,0), 
CURRENTITEM = "Sales Opex" , DIVIDE ( [Total Sales NP],1000,0), 
CURRENTITEM = "Marketing Opex" , DIVIDE ( [Total Marketing NP],1000,0), 
CURRENTITEM = "G&A Opex" , DIVIDE ( [Total G&A NP],1000,0), 
CURRENTITEM = "Restructuring/Amortization" , DIVIDE ( [Restructuring/Amortization NP],1000,0),
CURRENTITEM = "Total Operating Expense" , DIVIDE ( [Total Operating Expense NP],1000,0),
CURRENTITEM = "Non-Op", DIVIDE ( [Non-Op NP],1000,0), 
CURRENTITEM = "Transaction Gain/(Loss)", DIVIDE ( [Transaction Gain/(Loss) NP],1000,0), 
CURRENTITEM = "PBT", DIVIDE ( [PBT NP],1000,0), 
CURRENTITEM = "Income Tax Benefit/(Expense)", DIVIDE ( [Income Tax Benefit/(Expense) NP],1000,0), 
CURRENTITEM = "Net Income / (Loss)", DIVIDE ( [Net Income/(Loss) NP],1000,0),
CURRENTITEM = "Net Profit %" , [Net Income% NP] , 

      BLANK () )

RETURN
SelectedYearValues , 
    ALL( Dates ),
    ALL( Scenario ),
    TREATAS( VALUES( 'Dates-A'[Dates-A] ), Dates[Dates] ),
    TREATAS( VALUES( 'Scenario-A'[Scenario-A] ), Scenario[Scenario] ) )




VAR ScenarioB =
CALCULATE(
VAR CURRENTITEM = SELECTEDVALUE ('Financial_Template'[Normalized Account]) 

VAR SelectedYearValues = 
SWITCH (TRUE (),
CURRENTITEM = "Total Revenues", DIVIDE ( [Revenues NP],1000,0), 


CURRENTITEM = "Total COGS", DIVIDE ([COS_NP],1000,0), 
CURRENTITEM = "Total Operating Expense", DIVIDE ( [Total Operating Expense NP],1000,0), 
CURRENTITEM = "Total OIBT", DIVIDE ( [Total OIBT NP],1000,0), 
CURRENTITEM = "Total Gross Profit" , DIVIDE ( [Gross Profit NP],1000,0),
CURRENTITEM = "Gross Profit %" , [GP Margin NP] , 
CURRENTITEM = "R&D Opex" , DIVIDE ( [Total R&D NP],1000,0), 
CURRENTITEM = "Sales Opex" , DIVIDE ( [Total Sales NP],1000,0), 
CURRENTITEM = "Marketing Opex" , DIVIDE ( [Total Marketing NP],1000,0), 
CURRENTITEM = "G&A Opex" , DIVIDE ( [Total G&A NP],1000,0), 
CURRENTITEM = "Restructuring/Amortization" , DIVIDE ( [Restructuring/Amortization NP],1000,0),
CURRENTITEM = "Total Operating Expense" , DIVIDE ( [Total Operating Expense NP],1000,0),
CURRENTITEM = "Non-Op", DIVIDE ( [Non-Op NP],1000,0), 
CURRENTITEM = "Transaction Gain/(Loss)", DIVIDE ( [Transaction Gain/(Loss) NP],1000,0), 
CURRENTITEM = "PBT", DIVIDE ( [PBT NP],1000,0), 
CURRENTITEM = "Income Tax Benefit/(Expense)", DIVIDE ( [Income Tax Benefit/(Expense) NP],1000,0), 
CURRENTITEM = "Net Income / (Loss)", DIVIDE ( [Net Income/(Loss) NP],1000,0),
CURRENTITEM = "Net Profit %" , [Net Income% NP] , 

      BLANK () )

RETURN
SelectedYearValues , 
    ALL( Dates ),
    ALL( Scenario ),
    TREATAS( VALUES( 'Dates-B'[Dates-B] ), Dates[Dates] ),
    TREATAS( VALUES( 'Scenario-B'[Scenario-B] ), Scenario[Scenario] ) )




RETURN
DIVIDE( ScenarioA - ScenarioB , [Net Income% NP] , 0 )

I guess after a lengthiest explanation you might want to consider having a great and in-depth look at a file. So I’m also attaching the working of the PBIX file for the reference.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

Financial Reporting Test - Harsh.pbix (977.1 KB)

[EnterpriseDNA]

Hi @lulumoon, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

[lulumoon]

This is exactly what I needed! Thank you so much!!! Can’t express how much gratitude I have for you!

[Harsh]

Hello @lulumoon,

You’re Welcome.

I’m glad that I was able to help you and was able to provide the solution that met your requirements.

Thanks and Warm Regards,
Harsh