cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## Measure from data in one column

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)?

1 ACCEPTED SOLUTION
Super User

@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")
)
)``````

Did I answer your question? Mark my post as a solution! and hit thumbs up
16 REPLIES 16
Helper II

@FrankAT @Fowmy  thank for help. Will your formula work if there are many date periods in "Line"?

Super User

@Oleg222

Yes, it should work for dates as well with many date periods as well.

Did I answer your question? Mark my post as a solution! and hit thumbs up
Helper II

Sorry friends, one important think (I forgot, my fault) - in column "Line" should stand date.

Community Champion

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)

Super User

@Oleg222

``````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 )``````
Did I answer your question? Mark my post as a solution! and hit thumbs up
Helper II

@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?

Super User

@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")
)
)``````

Did I answer your question? Mark my post as a solution! and hit thumbs up
Helper II

@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?

Super User

@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?

Did I answer your question? Mark my post as a solution! and hit thumbs up
Helper II

@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?

temp3 =
DIVIDE(
SUMX(VALUES(Table3[Date]),
CALCULATE( SUM(Table3[Value]), Table3[Measures] = "Efficiency1") *
CALCULATE( SUM(Table3[Value]), Table3[Measures] = "Weight1")
,
SUMX( VALUES('Сбор данных'[Date]),
IF(CALCULATE( SUM(Table3[Value]), Table3[Measures] = "Efficiency1"), 1) *
CALCULATE( SUM(Table3[Value]), Table3[Measures] = "Weight1")
)

Super User

@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
)``````
Did I answer your question? Mark my post as a solution! and hit thumbs up
Helper II

@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.

Super User

@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.

Did I answer your question? Mark my post as a solution! and hit thumbs up
Helper II

@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.

Super User

@Oleg222

Can you reshare the sample data with this zero scenario with the expected results as well?

Did I answer your question? Mark my post as a solution! and hit thumbs up
Helper II

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors