Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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
Proud to be a Super User!
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
Proud to be a Super User!
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.
Proud to be a Super User!
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
Proud to be a Super User!
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?
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
Proud to be a Super User!
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
Proud to be a Super User!
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.
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
Proud to be a Super User!
But how exactly can I change the measure so that the Var _kpi fetch only the KPI being selected?
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
Proud to be a Super User!
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!
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
Proud to be a Super User!
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?
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
Proud to be a Super User!
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
Proud to be a Super User!
Hi @rcmv85 ,
Thank you for the last visual. That makes it clearer. Let me work on it today.
Nathaniel
Proud to be a Super User!
Hi @rcmv85 ,
I am at work, but will try to get back to you, today!
Thank you,
Nathaniel
Proud to be a Super User!
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
99 | |
88 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |