Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
I have table:
Line Indicator Value
A Efficiency 90
B Efficiency 80
A Weight 5
B Weight 10
I am trying to plot a metric that calculates the weighted average of some KPIs, but all the data is in one column.
And this measure should return me 83.33, from (90 * 5 + 80 * 10) / (5 + 10).
How can I use DAX to get this result (Without unpivot)?
Solved! Go to Solution.
@Oleg222
Can you try this version?
Weighted Average =
DIVIDE(
SUMX(
VALUES(Table3[Date]),
CALCULATE( SUM(Table3[Value]), Table3[Measures] = "Efficiency1") *
CALCULATE( SUM(Table3[Value]), Table3[Measures] = "Weight1")
)
,
SUMX(
VALUES(Table3[Date]),
CALCULATE( SUM(Table3[Value]), Table3[Measures] = "Weight1")
)
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Oleg222
Yes, it should work for dates as well with many date periods as well.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Sorry friends, one important think (I forgot, my fault) - in column "Line" should stand date.
Hi @Oleg222 ,
you can do it with DAX like this:
Weighted Average = VAR _Table =
SUMMARIZE (
'Table',
'Table'[Line],
"Efficiency", CALCULATE ( MIN ( 'Table'[Value] ), 'Table'[Indicator] = "Efficiency" ),
"Weight", CALCULATE ( MIN ( 'Table'[Value] ), 'Table'[Indicator] = "Weight" )
)
VAR _SUM =
SUMX ( _Table, [Efficiency] * [Weight] )
VAR _Result =
DIVIDE ( _SUM, SUMX ( _Table, [Weight] ) )
RETURN
_Result
With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)
@Oleg222
Created the measure, please check with your data:
Weighted Average =
var __num =
SUMX(
FILTER( Table3 , Table3[Indicator] = "Efficiency" ),
var __line = Table3[Line]
var __value = Table3[Value]
return
CALCULATE(
SUM(Table3[Value]),
FILTER(
Table3,
Table3[Indicator] = "Weight" && Table3[Line] = __line
)
) * __value
)
var __den =
SUMX(
FILTER( Table3 , Table3[Indicator] = "Efficiency" ),
var __line = Table3[Line] return
CALCULATE(
SUM(Table3[Value]),
FILTER(
Table3,
Table3[Indicator] = "Weight" && Table3[Line] = __line
)
)
)
return
DIVIDE( __num , __den )
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Fowmy Your measure almost works, with one exception - when on the same day for the indicator "Efficiency"" there is the same indicator (but it is zero), when calculating the denominator ( var __den), the total sum per day is doubled. Please tell me how to fix it?
@Oleg222
Can you try this version?
Weighted Average =
DIVIDE(
SUMX(
VALUES(Table3[Date]),
CALCULATE( SUM(Table3[Value]), Table3[Measures] = "Efficiency1") *
CALCULATE( SUM(Table3[Value]), Table3[Measures] = "Weight1")
)
,
SUMX(
VALUES(Table3[Date]),
CALCULATE( SUM(Table3[Value]), Table3[Measures] = "Weight1")
)
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Fowmy Your formula works, you helped me a lot. The last question - when calculating each indicator there is one more thing - the formula should count only when Efficiency1 <> 0. Tell me how to put a filter in your formula?
@Oleg222
On the 25th I see one set having zero for both Efficiency and Weight
Do you want to exclude all the lines that have zero?
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Fowmy No, I plan to delete the zeros altogether in the future. For example - 03/25/2021 Efficiency = 0 but Weight is not equal to zero and then the denominator will increase, although this should not be. My version of the formula - is there a better filtering option?
@Oleg222
Try this:
Weighted Average =
CALCULATE(
DIVIDE(
SUMX(
VALUES(Table3[Date]),
CALCULATE( SUM(Table3[Value]), Table3[Measures] = "Efficiency1") *
CALCULATE( SUM(Table3[Value]), Table3[Measures] = "Weight1")
)
,
SUMX(
VALUES(Table3[Date]),
CALCULATE( SUM(Table3[Value]), Table3[Measures] = "Weight1")
)
),
Table3[Value] <>0
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Fowmy unfortunately it doesn't work. For example - 09.06.2021 Efficiency1 = 0, Weight1 = 300. We remove all zeros with the filter. When counting, the value Weight1 = 300 is added to the denominator, but it shouldn't.
@Oleg222
What would be the logic to remove the data?
I assumed you always have either both zero or both have values. You can either fix your data or tell me the correct logic to apply.
Provide sample data with the expected scenario as well.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Fowmy sorry about that, below is the file in which everything is fixed. Considering that for 25.03, 28.03, 31.03 Efficiency is absent - Weight should not be added to the denominator (highlighted in red). In the table there are 3 options for the measure - the first (more complex but with the correct final result), the second (the best, in which we need to add a filter by dates where Efficiency is absent, the total is not correct) and the third (the second formula I corrected). I think my fixes can be done better.
https://drive.google.com/file/d/1QNNOtN4jmSF1E3qtk_w9YvNGPNYRnY0r/view?usp=sharing
@Oleg222
Can you reshare the sample data with this zero scenario with the expected results as well?
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
112 | |
89 | |
82 | |
76 | |
64 |
User | Count |
---|---|
142 | |
111 | |
108 | |
99 | |
95 |