Like-for-like chart based on dynamic slicer - how to filter out companies with missing data points?

[Anonymous]

Hi all,

Currently I'm struggling a bit and I hope someone can help me with the following.

Summary: I'm trying to find a solution to make charts like-for-like, i.e. when looking at trends over time, a company that has no data for one of the FYs I select should be left out of the visualisation. I have company data for different fiscal years. E.g. for company 1 FY17-FY20, while for company 2 FY16-FY19. If I select FY17-FY20 in my slicer, I only want companies that have data points for all these years to show up. This means that, even though company 2 has data for FY17-FY19, it should be left out as FY20 data is missing.

I created a dummy dataset that looks like this:

In Power BI I created a measure for gross margin %:

Gross margin % = SUM ( [Gross profit] ) / SUM( [Revenue] )

I filter out FY16:

The chart and data looks like this:

However, company 3 is taken account, distorting the trend. Like-for-like, the chart and data looks like this:

I created a calculated table as a first step to be able to filter company 3 out later, but here is where I get stuck.

1. Availability Table = CROSSJOIN ( DISTINCT ( 'Example'[Company] ),
   DISTINCT ( Example[Fiscal year] ) )
2. Gross margin % = CALCULATE (
   [Gross margin %],
   FILTER (
   'Example',
   'Availability Table'[Company] = 'Example'[Company]
   && 'Availability Table'[Fiscal year] = 'Example'[Fiscal year] ) )
   
3. Gross margin % availability = IF ( [Gross margin %] = 0, 0, 1 )

So now I have an overview showing for which FYs GM % is available per company.  I then need the flexibility of a measure to be able to filter on different FYs in my slicer to make this work (e.g. calculate sum of 'gross margin % availability' from table filtered based on the slicer selection and check if this is the same as # of FYs selected or e.g. check for 0s in this filtered dataset), but you cannot use a measure as filter. Do you know how I can make this work?

Thanks a lot in advance.

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

Please check the below picture and the sample pbix file's link down below.

I am not sure how your full data model looks like, but in my opinion, the FY number column (number format) is needed in order to calculate correctly.

the measure is in the sample pbix file.

https://www.dropbox.com/s/evn8gbj9z00a98w/LFuhler.pbix?dl=0 

Gross Margin % only full FY =
VAR yearscount =
CALCULATE (
DISTINCTCOUNT ( 'Table'[Fiscal year] ),
ALLSELECTED ( 'Table'[Fiscal year] )
)
VAR yearsmin =
CALCULATE (
MIN ( 'Table'[fiscal year number] ),
ALLSELECTED ( 'Table'[Fiscal year] )
)
VAR yearsmax =
CALCULATE (
MAX ( 'Table'[fiscal year number] ),
ALLSELECTED ( 'Table'[Fiscal year] )
)
VAR newtable =
FILTER (
GROUPBY (
FILTER (
ALL ( 'Table' ),
'Table'[fiscal year number] >= yearsmin
&& 'Table'[fiscal year number] <= yearsmax
),
'Table'[Company],
"@fiscalyearcount", SUMX ( CURRENTGROUP (), 1 )
),
[@fiscalyearcount] = yearscount
)
VAR profitandrevenue =
SUMMARIZE (
newtable,
'Table'[Company],
"@profit", SUM ( 'Table'[Gross Profit] ),
"@rev", SUM ( 'Table'[Revenue] )
)
RETURN
SUMX ( profitandrevenue, [@profit] ) / SUMX ( profitandrevenue, [@rev] )

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: https://www.linkedin.com/in/jihwankim1975/

[k-squared]

Jihwan Kim, thank you for the great solution here.

I'm working on something similar and was wondering if you knew of a way to turn the margin calculation above into a filter of some sort. So, using this thread as an example, is there a way to have a "same reporter" flag for the visual where the user can only include companies with all the relevant data in every time period? I have found a way to do this when I explicitly show the companies in the visual but can't make the same formula work when I just want to show the aggregated result. 

That way, (I think) a user could use their formulas for sales growth, margins, etc. and just turn on the same-reporter filter when needed. Any ideas?

Also, I'm working with a ~10mm row data set so am bumping into the 1mm row limit on calculations when I want to view the data over a longer time frame. Do you have any thoughts on workarounds? 

FYI, I was able to fit your DAX code above to my use case, though I did need to replace the ALL with ALLSELECTED given the size of the data set I'm working with but I think it's still working for my setup. 

[Anonymous]

Hi @Jihwan_Kim,

Thank you for your quick and helpful response. I wanted to upload my example files as well, but didn't manage to find out how I could do this when posting, so sorry about that. Thanks for taking the time to replicate the data.

I'm quite new to Power BI, so let me check of I understand your approach correctly.

You create variables for # years, minimum and maximum year. Then create the newtable variable by filtering the dataset down to everything between and including the min and max years > group by company > add column that counts and sums # company rows > filtering down again on condition that the # rows = yearscount. Then create the profitandrevenue variable with column 'company' and columns containing gross profit and revenue amounts. The result of the measure is the division of the gross profit column by the revenue column. Is that correct?

It does exactly what I'm looking for, so thanks for sharing.

I have two additional questions:

1. I want to further build this out by adding new KPIs and the dataset I will be using will be larger. I understood using SUMX slows down your PBI report. At what amount of KPIs will using this approach have a significant impact on performance and is there a way to minimise this?
2. In case I want to show e.g. gross margin % and EBITDA margin % in one chart, I want the data to be coming from the same companies. If I use your approach and set up a measure for EBITDA margin % and plot both KPIs in a chart, I assume the numbers might be coming from two different sets of companies (e.g. if for one company EBITDA margin % is not available, the sample size will be smaller than for GM %). Is this correct? Do you know a solution to this?

Hope this is clear, otherwise let me know.

Thanks in advance.

[Jihwan_Kim]

Hi, @Anonymous 

Thank you for your feedback.

- your understanding is correct.

- In the measure, because the virtual table (newtable) is created, I had no way but to use SUMX function in order to calculate the newtable's information. If the newtable is not a virtual table, and if it is actually created, of course, no need to use sumx. However, I am not sure whether actually creating the newtable is possible or not if you want to dynamically calculate the result by selecting the year from the slicer.

- Sorry that I quite do not understand your last question. If you can show the expected outcome by using the same sample data, it will be very much helpful for me to understand.

Thank you.

[Anonymous]

Hi @Jihwan_Kim,

Thanks a lot again. I have been trying to respond a few times, but I keep getting error messages regarding issues in the HTML code of my post. I hope I will be able to resolve this soon.

[Anonymous]

While I was adjusting my example file, I noticed there's actually two things I would like to know. I will explain my questions by describing 2 situations below. I'm afraid I can't share my dataset as I don't see an upload button here and my laptop does not allow me to share via other ways. Hopefully it works for you if I explain the steps I took.

I updated the dummy dataset to look like this:

And added a measure for EBITDA:

[similar to Gross margin %]

Slicer FY17-FY20 (leave out FY16):

Current dataset (margin % are not correct per company, but this is fine as in my chart I want to show it on an aggregated level):

Situation 1

I create a line chart for EBITDA margin % which looks like this:

However, for company 2 there is no EBITDA data so it should not be included. EBITDA margin % calculation (7+0) / (100 + 150) = 2.8% for FY17. In case company 2 is left out, only company 1 is left and the chart should look like this:

How can I achieve that also companies that do not have enough data to calculate the measure are left out of the visualisation?

I hope this is clear, otherwise please let me know.

I will explain situation 2 in my next message.

[Jihwan_Kim]

Hi, @Anonymous 

Please check the below picture and the sample pbix file's link, whether I correctly followed your instruction.

I chose to filter out one more time inside the same measure if the EBITDA value is zero.

You can check this in the 30~36th line in the measure.

https://www.dropbox.com/s/evn8gbj9z00a98w/LFuhler.pbix?dl=0 

EBITDA Margin % only full FY and filterout zeroEBITDA =

VAR yearscount =

CALCULATE (

DISTINCTCOUNT ( 'Table'[Fiscal year] ),

ALLSELECTED ( 'Table'[Fiscal year] )

)

VAR yearsmin =

CALCULATE (

MIN ( 'Table'[fiscal year number] ),

ALLSELECTED ( 'Table'[Fiscal year] )

)

VAR yearsmax =

CALCULATE (

MAX ( 'Table'[fiscal year number] ),

ALLSELECTED ( 'Table'[Fiscal year] )

)

VAR newtable =

FILTER (

GROUPBY (

FILTER (

ALL ( 'Table' ),

'Table'[fiscal year number] >= yearsmin

&& 'Table'[fiscal year number] <= yearsmax

),

'Table'[Company],

"@fiscalyearcount", SUMX ( CURRENTGROUP (), 1 )

),

[@fiscalyearcount] = yearscount

)

VAR ebitdaandrevenuefilteroutzeroEBITDA =

FILTER(SUMMARIZE (

newtable,

'Table'[Company],

"@ebitda", SUM ( 'Table'[EBITDA]),

"@rev", SUM ( 'Table'[Revenue] )

), [@ebitda] >0) // filter out where EBITDA is zero

RETURN

SUMX ( ebitdaandrevenuefilteroutzeroEBITDA, [@ebitda] / [@rev] )

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Anonymous]

Hi @Jihwan_Kim,

Many thanks again for your prompt response, much appreciated!

I will do some testing to see if any other issues arise, but this seems to be a good solution.

In my previous message I mentioned I had a second question which I wanted to describe by outlining that situation. Please find below situation 2.

Situation 2

I have the same dataset and slicer settings as in situation 1. However, now I want to show both Gross margin % and EBITDA margin % in one chart. What will happen is that Gross margin % will be calculated based on 2 companies, while EBITDA margin % is based on 1 company.

I actually have 2 questions:

2a) What is the easiest way to visualise how many companies are included in the calculation of the measure?

What I mean by this is that, in case the lines are based on a different sample of companies, I want to be able to see how many companies the calculated measures are based on. Below a simplified example:

How I set up this measure was by tweaking yours to count the # companies:

Company Count EBITDA Margin % =

VAR yearscount =

CALCULATE (

DISTINCTCOUNT ( 'Table'[Fiscal year] ),

ALLSELECTED ( 'Table'[Fiscal year] )

)

VAR yearsmin =

CALCULATE (

MIN ( 'Table'[fiscal year number] ),

ALLSELECTED ( 'Table'[Fiscal year] )

)

VAR yearsmax =

CALCULATE (

MAX ( 'Table'[fiscal year number] ),

ALLSELECTED ( 'Table'[Fiscal year] )

)

VAR newtable =

FILTER (

GROUPBY (

FILTER (

ALL ( 'Table' ),

'Table'[fiscal year number] >= yearsmin

&& 'Table'[fiscal year number] <= yearsmax

),

'Table'[Company],

"@fiscalyearcount", SUMX ( CURRENTGROUP (), 1 )

),

[@fiscalyearcount] = yearscount

)

VAR EBITDAandrevenue =

FILTER (

SUMMARIZE (

newtable,

'Table'[Company],

"@EBITDA", SUM ( 'Table'[EBITDA] ),

"@rev", SUM ( 'Table'[Revenue] )

),

[@EBITDA] <> 0

)

RETURN

COUNTX ( EBITDAandrevenue, [Company] )

However, I can imagine the PBI report will become slow if I have to create a separate measure for every existing measure that calculates how many companies the existing measure is based on. Would there be a nicer way to do this?

2b) How can I make sure the 2 lines in the chart are based on the same companies?

I might want to compare the Gross margin % trend and the EBITDA margin % trend, but when these are based on a different set of companies, the comparison is not like-for-like. Would it be possible to set up the measures in such a way that only companies are included that have data for both measures?

For the current dummy dataset this means that only company 1 will show up:

I hope this is clear, otherwise please let me know.

Again, thank you. This really helps!

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

Thank you for your message.

Q 2a) I think you are right. In my opinion, each calculation shows different information, so there is not much chance to reduce the number of measures.

Q 2b) I created the new version (V2) of gross margin % that suits the situation. (2-1)
And not like Q 2a), because it is the same companies' (or company's) information, it can be combined into one measure if there is one more simple table for the LEGEND in the chart (2-2). Additionally, I added a tooltip function to show the company count. Once you hover over the mouse on the line, the company count will show up.

Going back the Q 2a), it can be done in a similar way to reduce the number of measures, however, the length of each measure will be too long, and it will become hard to understand, I think....
Please check the below picture and the link down below.

https://www.dropbox.com/s/evn8gbj9z00a98w/LFuhler.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Anonymous]

Hi @Jihwan_Kim,

Thanks a lot for your reply again. I am impressed!

I see it's getting a bit complicated regarding the length of the measures. I hope it will not affect performance too much, but I have to test that. I tried applying your approach to a different dataset also containing months, but then I get an error as FYs are counted * 12. I added a column 'Data type' to make a distinction between monthly and annual data and set the report filter to 'Annual' as I only want to include this data. However, months still seem to be counted.

I tried removing the ALL function in newtable, but that is not solving the issue (intermediate question: why do we need the ALL function here?).

Do you know why the measure does not seem to take into account the report filter?

Many thanks in advance.

[Jihwan_Kim]

Hi, @Anonymous 

Thank you for your message.

- The reason ALL is inside VAR newtable is because to filter out the company that does not meet the first requirement, for instance, if the year range is between 17 ~ 20, then the data must be in four lines and filter out the company that has less than four lines of information.

- if you put the month information into the table, then the counting line concept has to be changed. The measure is counting how many years. But newtable has month-line so it does not fit. I am not sure whether your real data also has the day-level information or not. In this case (month level information), I think it is better to use Dim-Date-Table in order to manage well the measures that are related to year/month/day...

If it is OK with you, please share your month level or day level sample file's link here, then I can try to use Dim-Date-Table and come up with simpler measures (I hope).

Thank you.

[Anonymous]

Hi @Jihwan_Kim,

Thank you again.

- Regarding the ALL function, is the filtering out of the companies not in the FY17-FY20 range not already done just by the slicer selection? I see the measure does not work when I remove the ALL function, but I don't understand yet why this is. Just trying to understand what is happening in the measure.

- I'm still experimenting a bit with the dataset. I will let you know the outcomes and get back to you in case it's really necessary to filter out the monthly data.

Thank you!

[Jihwan_Kim]

Hi, @Anonymous 

Thank you for your feedback.

I think it makes it better to understand if the visualization is changed to the table.

If ALL is not used, then each row is only considering one year.

So, inside the measure, everything becomes a one-year count, and all are not matching with a four-year count.

I hope I explained well.

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

Please check the below picture and the sample pbix file's link down below.

I am not sure how your full data model looks like, but in my opinion, the FY number column (number format) is needed in order to calculate correctly.

the measure is in the sample pbix file.

https://www.dropbox.com/s/evn8gbj9z00a98w/LFuhler.pbix?dl=0 

Gross Margin % only full FY =
VAR yearscount =
CALCULATE (
DISTINCTCOUNT ( 'Table'[Fiscal year] ),
ALLSELECTED ( 'Table'[Fiscal year] )
)
VAR yearsmin =
CALCULATE (
MIN ( 'Table'[fiscal year number] ),
ALLSELECTED ( 'Table'[Fiscal year] )
)
VAR yearsmax =
CALCULATE (
MAX ( 'Table'[fiscal year number] ),
ALLSELECTED ( 'Table'[Fiscal year] )
)
VAR newtable =
FILTER (
GROUPBY (
FILTER (
ALL ( 'Table' ),
'Table'[fiscal year number] >= yearsmin
&& 'Table'[fiscal year number] <= yearsmax
),
'Table'[Company],
"@fiscalyearcount", SUMX ( CURRENTGROUP (), 1 )
),
[@fiscalyearcount] = yearscount
)
VAR profitandrevenue =
SUMMARIZE (
newtable,
'Table'[Company],
"@profit", SUM ( 'Table'[Gross Profit] ),
"@rev", SUM ( 'Table'[Revenue] )
)
RETURN
SUMX ( profitandrevenue, [@profit] ) / SUMX ( profitandrevenue, [@rev] )

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: https://www.linkedin.com/in/jihwankim1975/