How to calculate an average metric for this specific fact table?

[rcmv85]

Hi folks!

In my power BI model I have a fact table that looks like the table below but with many more companies and many more KPIs.

I would like to create a metric that could give me the average value of each KPI, of all companies for each year.

For instance, with this metric I would be able to get the average revenue during 2022 of Apple and Samsung together i.e :

Average Revenue for 2022 = (1,000,000,599.00 + 1,100,036,599.00) / 2 = 1,050,018,599.00

This average metric would be applied for all KPIs.

Thanks in advance

KPI	YEAR	COMPANY	VALUE
REVENUE	2022	APPLE	1,000,000,599.00
REVENUE	2022	SAMSUNG	1,100,036,599.00
REVENUE	2021	APPLE	2,000,015,694.00
REVENUE	2021	SAMSUNG	1,050,369,589.00
INCOME	2022	APPLE	525,603.00
INCOME	2022	SAMSUNG	259,874.00
INCOME	2021	APPLE	365,000.00
INCOME	2021	SAMSUNG	235,000.00
ASSETS	2022	APPLE	12,650,024,580.00
ASSETS	2022	SAMSUNG	22,650,024,580.00
ASSETS	2021	APPLE	52,350,152,358.00
ASSETS	2021	SAMSUNG	56,035,848,920.00

[Nathaniel_C · Accepted solution]

Probably by Instead of 

VAR _kpi =
     Max(TableKPI[KPI])  //get this row's KPI

Write this

VAR _kpi = "Revenue"
     if revenue is what you wsnt - and should be proper case

This replaces 1 line from the measure that I gave you. You will have three measures now, one for each KPI.  Please mark this as solved so that others can find it easily.

Thank you,

Nathaniel

[bolfri]

Ultimate measure that will work for all the columns that you've provided.

On Totals you will get the averge of the values within KPI's or YEAR used.

Average per Year & KPI = 
CALCULATE(
    AVERAGEX(GROUPBY(SampleData,SampleData[KPI],SampleData[YEAR]),AVERAGE(SampleData[VALUE])),
    ALLEXCEPT(SampleData,SampleData[YEAR],SampleData[KPI])
)

Results:

Note that will show same value for all companies that you have so you can crete a simple measure like: below/above average, gap to avg etc. 

If you don't want this behavior and want to get exact values even using company column then it's simply:

Average per Year & KPI = AVERAGEX(GROUPBY(SampleData,SampleData[KPI],SampleData[YEAR]),AVERAGE(SampleData[VALUE]))

Results:

Note that both measures show same results building visualisation with KPI and YEAR's only.

[Nathaniel_C]

Hi @rcmv85 
Please try this:

Average KPI = 
VAR _year =
    MAX ( TableKPI[YEAR] ) //get this row's Year
VAR _kpi =
     Max(TableKPI[KPI])  //get this row's KPI
VAR _calc =
    CALCULATE (
        [Avg of Value],
        FILTER ( ALL ( TableKPI ), TableKPI[YEAR] = _year&& TableKPI[KPI] = _kpi )
    )
RETURN
    _calc

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[rcmv85]

Hi @Nathaniel_C 

Your code work but has a catch. If I create a visualization to present me the revenue values but linked to a filter where I select only 3 companies instead of all the companies in the table, the metric will still present me the average for all the 10 companies instead of the selected 3 companies. Is there way to prevent this and make the average metric more smarter to adapt to a user input filter?

[Nathaniel_C]

Hi @rcmv85 

Concate = CONCATENATEX(VALUES(TableKPI[COMPANY]),TableKPI[COMPANY], ", ")

Average KPI Working = 
VAR _year =
    MAX ( TableKPI[YEAR] ) //get this row's Year
VAR _kpi =
     Max(TableKPI[KPI])  //get this row's KPI
//Var _list = [Concate]

VAR _calc =
    CALCULATE (
        [Avg of Value],
        FILTER ( ALLSELECTED(  TableKPI ), TableKPI[YEAR] = _year && TableKPI[KPI] = _kpi && TableKPI[COMPANY] = [Concate])
    )
RETURN
    _calc 

With all values selected:

With only two:

Use these two new measures in place of the previous measure.

Let me know how it goes!
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[Nathaniel_C]

Hi @rcmv85 ,

Here is my pbix https://1drv.ms/u/s!AgCd7AyfqZtE4Ui3L2fUtNM-WJN6?e=mRpWv9 


Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[rcmv85]

Hi @Nathaniel_C !

I dont think both measures are working properly or at least I'm not implementing them the right way (this is very likely to be happening lol).

The first metric you gave is actually calculating the average of all companies but only for the Revenue (the first KPI). I have a visual where I'm only presenting the Income KPI and the average that is being calculated is the one from the revenue KPI (screenshot below):

The second metric you presented (which the purpose was to calculate only the average for only the selected companies), does in fact a calculation for the selected companies but is also locked on the Revenue KPI. In both screenshots I'm presenting the Income KPI but the average values are the ones from the Revenue. 

Btw thank a lot for the effort. I feel that your solution is very close to what I want but I'm not able to spot the bug. 

[Nathaniel_C]

Hi @rcmv85 ,
After a quick look at this, I may have the answer.  The table that you gave us, include a column for the different KPIs and that is why I included the KPI slicer in mine. Since you are only showing one type of KPI, try changing the measure , so that the second Var _kpi, only = the KPI that you are showing.  (= "Revenue" for example)
Let me know!

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[rcmv85]

But how exactly can I change the measure so that the Var _kpi fetch only the KPI being selected?

[Nathaniel_C · Accepted solution]

Probably by Instead of 

VAR _kpi =
     Max(TableKPI[KPI])  //get this row's KPI

Write this

VAR _kpi = "Revenue"
     if revenue is what you wsnt - and should be proper case

This replaces 1 line from the measure that I gave you. You will have three measures now, one for each KPI.  Please mark this as solved so that others can find it easily.

Thank you,

Nathaniel

[rcmv85]

Hi @Nathaniel_C !

Thank you for the solution. That works!

I was wondering though, if there is a way to create one single average metric that adapts to the company filter, and that can read the KPI being presented on the visualization, instead of multiple average metrics (one for each KPI)?

Would this be feasible?

Thanks!

[Nathaniel_C · Accepted solution]

Hi @rcmv85 ,
So what I see in the visual is different tables for each KPI. A slicer for the company, and for the year.  Since you have the company slicer, we don't need the concat measure.  I wrote another measure without it.
Here is my pbix.

https://1drv.ms/u/s!AgCd7AyfqZtE4Ui3L2fUtNM-WJN6?e=AOSN4N 

Please go to the "Final" page in the pbix.  You will see a different visualfor each KPI and two slicers. Note that if you do not select a slicer item, you will get everything available. Here is the measure without [Concate].

Average KPI Working No Concate = 
VAR _year =
    MAX ( TableKPI[YEAR] ) //get this row's Year
VAR _kpi =
     Max(TableKPI[KPI])  //get this row's KPI


VAR _calc =
    CALCULATE (
        [Avg of Value],
        FILTER ( ALLSELECTED(  TableKPI ), TableKPI[YEAR] = _year && TableKPI[KPI] = _kpi )
    )
RETURN
    _calc 

Please confirm that this works for you.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[rcmv85]

Hi @Nathaniel_C !

Yes, that worked. Neverthless it is good to have a real example of the application of a concatenatex function is this context. It may be usefull for other metrics to be implemented.

One last question. I'm testing that new average metric on some visuals but I'm getting duplicates for each company (please see screenshot below).

Is there a way to avoid this and present the average only one time?

[Nathaniel_C]

Hi @rcmv85 ,
We could add to the measure, a condition that gets all the company names, figure out either the min or max company name (closest to A, or closest to Z) and only put the average one time into the table.  That  would make the table cleaner, but then the average  would show up in the graph for only company, which would be strange looking. If you had one graph with the just totals graphed, you could put the average measure into a card and just overlay the graph with the card as a number.  So the KPIs would be graphical and the average would be text only.

What do you think?

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[Nathaniel_C]

Hi @rcmv85 ,
Here is a picture showing using the card idea from above, and also a second idea, dropping the measure in the tooltips.

Let me know what you think.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[Nathaniel_C]

Hi @rcmv85 ,
Thank you for the last visual.  That makes it clearer.  Let me work on it today.

Nathaniel

[Nathaniel_C]

Hi @rcmv85 ,
I am at work, but will try to get back to you, today!

Thank you,

Nathaniel

[vicky_]

Try something like:

Measure = CALCULATE(AVERAGE([Value]), ALLEXCEPT(Table, Table[KPI]))

That or you could consider pivoting your data and using the built-in average functions in your visuals.