Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Oleg222
Helper II
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

@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


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

16 REPLIES 16
Oleg222
Helper II
Helper II

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

@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


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Oleg222
Helper II
Helper II

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

FrankAT
Community Champion
Community Champion

Hi @Oleg222 ,

you can do it with DAX like this:

 

08-06-_2021_00-00-24.png

 

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)

Fowmy
Super User
Super User

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


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

 

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


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?

Fowmy_0-1623265396311.png

 



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


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?

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

 

@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


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.






 

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


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?




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


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.