How to filter with date and another metric (i.e. % of revenue)

Power BI
1

Hi, I just completed the Financial reporting module and managed to replicate my data is the desired format successfully.

I am now trying to make my P&L table more flexible (i.e. to filter with date, but then also have the value column change between value or % of revenue). I have already created my % of revenue calculation for each line, but only way I can currently view it is if I add it as a separate column in my table.

How can I add a filter which allows to show either value OR % of revenue?

2

Hi Stuart,

Is it possible to share pbix file and desired output screen shot.

What i understood is you want to see a dynamic column either values or % right ?

Step 1 Create table with values Value, Percentage
Step 2 Use these values as slicer - slicer setting select single
Step 3 Create dynamic measure based on the slicer

IF Selected value is Values display values else percentage

3

Welcome to the forum @Stuart and thank you for posting your question. To receive a resolution in a timely manner please make sure that you provide all the necessary details on this thread.

Here is a potential list of additional information to include in this thread; demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

Including all of the above will likely enable a quick solution to your question.

4

Hello Stuart,

You can analyze the P&L Statement by switching between different metrics by following the steps -

  1. Create a table with the Metrics - Revenue and % of Revenue.

  2. Create a measure for a particular Metric Selected -

Metric Selected =
IF( HASONEVALUE( ‘Metric Selection’[Metric] ) ,
VALUES( ‘Metric Selection’[Metric] ) ,
“Revenues” )

Ensure that if in case nothing is selected you need to pass the default value otherwise the Visuals will show the error message. In this case, I’ve set “Revenues” as a default value if nothing is selected.

  1. Create a measure to show the values if a particular metric is selected -

Selection =
SWITCH( TRUE() ,
VALUES( ‘Metric Selection’[Metric] ) = “Revenues” , [Total Sales] ,
VALUES( ‘Metric Selection’[Metric] ) = “% of Revenues” , [% of Revenues] ,
[Total Sales] )

I’ve also attached a sample file for the reference.

Hoping this solution will meet your requirement.

Thanks and Warm Regards,
Harsh

Filter with Date and Another Metric.pbix (722.3 KB)

5

Hi Harsh

Still struggling to amend (proper PBI newbie). I set up as follows:
Metric Selected =
IF( HASONEVALUE( ‘Metric Selection’[Metric] ) ,
VALUES( ‘Metric Selection’[Metric] ) ,
“CY MTD” )

and Selection as:
Selection =
SWITCH( TRUE() ,
VALUES( ‘Metric Selection’[Metric] ) = “Value” , [CY MTD] ,
VALUES( ‘Metric Selection’[Metric] ) = “% of Revenue” , [CY % of Revenue] ,
[CY MTD] )

But this did not work. My CY MTD and % of Revenue DAX formalue are as follows:

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

RETURN
SWITCH( TRUE() ,
CURRENTITEM = “Total Revenues”, [Total Revenues] … etc.

Is the set up of my CY MTD / % of Revenue the issue in pulling this off?

Note “CY MTD” is my Current Year Month to Date.

6

Hello Stuart,

Under “SWITCH( TRUE () )” you can try to put the code as given below which in your case may vary due to naming conventions but logic will remain the same.

Totals Selection =

VAR CurrentItem = SELECTEDVALUE( 'Income Statement Template'[Items - Normalized] )

VAR Revenues = SWITCH( TRUE(),
    CurrentItem = "Total Revenues", DIVIDE( [Revenues], 1000, 0 ),
    CurrentItem = "Total COGS", DIVIDE( [COGS], 1000, 0 ),
    CurrentItem = "Total Gross Profit", DIVIDE( [Gross Profit], 1000, 0 ),
    CurrentItem = "Gross Profit %", FORMAT( [Gross Profit Margin], "0.00%" ),
    CurrentItem = "Total Other Expenses", DIVIDE( [Expenses], 1000, 0 ),
    CurrentItem = "Total Net Profit", DIVIDE( [Net Profit], 1000, 0 ),
    CurrentItem = "Net Profit %", FORMAT( [Net Profit Margin], "0.00%" ),
        CALCULATE( [Actuals (,000)],
            FILTER( 'Income Statement', 'Income Statement'[Items] = CurrentItem ) ) )

VAR %ofRevenues = SWITCH( TRUE(),
    CurrentItem = "Total Revenues", FORMAT( 1, "0.00%" ),
    CurrentItem = "Total COGS", FORMAT( DIVIDE( [COGS], [Revenues], 0 ), "0.00%" ),
    CurrentItem = "Total Gross Profit", FORMAT( DIVIDE( [Gross Profit], [Revenues], 0 ), "0.00%" ),
    CurrentItem = "Total Other Expenses", FORMAT( DIVIDE( [Expenses], [Revenues], 0 ),"0.00%" ),
    CurrentItem = "Total Net Profit", FORMAT( DIVIDE( [Net Profit], [Revenues], 0 ), "0.00%" ),
        FORMAT( DIVIDE( CALCULATE( [Actuals (,000)]  * 1000,
            FILTER( 'Income Statement', 'Income Statement'[Items] = CurrentItem ) ), [Revenues], 0 ), "0.00%" ))

RETURN
SWITCH( TRUE(),
    [Table Data] = "Revenues", Revenues,
    [Table Data] = "% of Revenue", %ofRevenue,
    BLANK() )

And then drag the created measure into the P&L Statement Template.

Hoping you will the desired result.

Thanks & Regards,
Harsh

7

Hello @Stuart

Please find the attached file of my working for P&L or Income Statement.

This may help you achieve the desired result which you are looking for.

Hoping this will meet your requirements.

Thanks & Warm Regards,
Harsh

Financial Reporting.pbix (807.9 KB)

8
  • Please be sure to check out our DAX Clean Up tool it’s a great way to ensure to make your DAX code easy to read. Thanks!

https://analysthub.enterprisedna.co/dax-clean-up

9

@Stuart

If you need some additional material you could checkout this article by Sam. He steps through the process as described by @Harsh.

10

Hi @Harsh

That is exactly what I am looking for, but after 4-hours of trying to replicate it in my model, I am close to throwing my laptop out the window.

Attached is a section of my file. Any chance you could redo the formalue on this and send it back?

Kind regards

Stuart

DNA_Test.pbix (563.1 KB)

11

Hello @Stuart,

Thank you for sharing the PBIX File.

I would like to know what does “PAR” and “POR” stand under the “Metric Selection” table.

For “% of Revenue” and “Actuals” I’ve worked it out. And there are some points which you can note -

  1. I’ve considered CY MTD as selection for the Actuals
  2. While calculating the “% of Revenue” there was some writing error while setting the variables such as “Rooms Revenues” was written as “Room Revenue” that’s why “% of Revenue” was showing blank for some of the row items.
  3. While setting variable for the template, wrong template was referenced since selected template was " ‘Summary P&L Template’[Income Statement Items]" and it was referenced to " 'Subject Data’[Income Statement Items]".
  4. Since Months are already there under the slicer we won’t to able to analyze it on the quarterly basis. I’ve kept both the solutions as “Quarterly” and “Monthly”
  5. And lastly you could have also created a relationship between original “Date” column from the dates table with the “Month & Year” of the Fact Data.

Please find the attached PBIX file of the working.

Hoping this will meet your requirement and can help you to achieve your analysis.

Thanks & Warm Regards,
Harsh

DNA_Test.pbix (578.2 KB)

12

Hi @Harsh

Managed to get to to work. Now trying to have another metric being “Period MTD OR YTD” and struggling to rewrite the formula. Below is my current formula, how do I add the “AND” function successfully? (Note I created another “Period data table” with MTD and YTD options)

CY MTD =
SWITCH( TRUE() ,
[Table Data] = “Actual” , [CY MTD actuals] ,
[Table Data] = “% TORE” , [CY MTD % TORE actuals] ,

BLANK() )

13

Hello @Stuart,

I’m bit confused here, so just wanted to ask that are there 2 slicers in the same visual and want to compare it using AND condition between them or it’s you want to put AND in the given formula.

Thanks & Warm Regards,
Harsh

14

Hi @Harsh

There are 2 slicers (excl. date), so need them in the same visual. What is the best formula to use?

Kind regards

Stuart

15

Hello @Stuart,

I’ve attached the PBIX file which contains the measure that uses AND condition.

Hoping this will help you to achieve your desired analysis.

Thanks & Regards,
Harsh

exp.pbix (67.9 KB)

16

Similar, but I want to do it with columns. i.e. if I select “MTD” in splicer 1 and “% revenue” in splicer 2, “MTD % revenue” column is pulled through. Could you run this on my previously shared file?

17

Hello @Stuart,

If possible can you share the PBIX File of your revised working so that it would be helpful since it shall contain the measures which has been already created.

Thanks & Regards,
Harsh

18

My file is already quite larger and complex, however, attached is the update sample. So what I need the Selected Metric column to change dependent on the Metric (i…e actual or % of revenue) and MTD or YTD metricDNA_Test_UPDATED.pbix (576.8 KB)

19

Hello @Stuart,

Still bit confused, because I’m not able to get the “CY MTD Actuals” and “CY MTD % TORE Actuals” in the shared PBIX file.

But as per my understanding, if you’re following the “Quarterly Solution Sheet”, then it will not be possible to make analysis since in the table numbers will be shown on Quarterly basis and slicers will be selected for either MTD or YTD.

And if you’re following the “Monthly Solution Sheet”, in that case if you’re selecting particular Year and Month from the date slicer then we can directly view the MTD numbers.

Thanks & Regards,
Harsh

20

Hello @Stuart,

As per my understanding, I’ve worked on the 2 slicers as per the stated requirement.

I’m also attaching the PBIX file for the reference.

Hoping this will help you to achieve your desired analysis that you are looking for.

Please let me know if in case I’ve missed something.

Thanks & Warm Regards,
Harsh

DNA_Test_UPDATED.pbix (578.8 KB)