Skip to main content
cancel
Showing results for 
Search instead 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

Reply
lennardheyder
Frequent Visitor

Weighted average without blanks

Hello everybody, 

 

I'm calculating a weighted average of a minimum price weighted by Sales on an article level.

There are however blanks in the list of minimum prices so I dont want to considers those articles at all in the calculation! As there is already "Keepfilters" used in the calculation, I cant apply another filter to exclude the blanks, any idea what to do?

 

Weighted Pricing Delta =
VAR __CATEGORY_VALUES = VALUES('MatHier'[Article name])
RETURN
    DIVIDE(
        SUMX(
            KEEPFILTERS(__CATEGORY_VALUES),
            CALCULATE(
                AVERAGE('PREISBAND'[Δ Pricing letter[CUR / UOW]]])
                    * SUM('OH01 + OS01'[NES 3rd party (€)])
            )
        ),
        SUMX(
            KEEPFILTERS(__CATEGORY_VALUES),
            CALCULATE(SUM('OH01 + OS01'[NES 3rd party (€)]))
        )
    )
1 ACCEPTED SOLUTION
v-chenwuz-msft
Community Support
Community Support

Hi @lennardheyder,

 

You can use calculatetable() to adjust the VAR __CATEGORY_VALUES

VAR __CATEGORY_VALUES= CALCULATETABLE(VALUES('MatHier'[Article name]), ALLNOBLANKROW('MatHier'[minimum prices]))

 

If does not work, please try this code or share your pbix file without sensitive data.

or try this code:

Weighted Pricing Delta =
VAR _s =
    SUMMARIZE (
        'MatHier',
        [Article name],
        "avg", AVERAGE ( 'PREISBAND'[Δ Pricing letter[CUR / UOW]]] ),
        "sum", SUM ( 'OH01 + OS01'[NES 3rd party (€)] )
    )
VAR _1 =
    SUMX ( FILTER ( _s, [avg] <> BLANK () ), [avg] * [sum] )
VAR _2 =
    SUMX ( FILTER ( _s, [avg] <> BLANK () ), [sum] )
RETURN
    DIVIDE ( _1, _2 )

 

Best Regards

Community Support Team _ chenwu zhu

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-chenwuz-msft
Community Support
Community Support

Hi @lennardheyder,

 

You can use calculatetable() to adjust the VAR __CATEGORY_VALUES

VAR __CATEGORY_VALUES= CALCULATETABLE(VALUES('MatHier'[Article name]), ALLNOBLANKROW('MatHier'[minimum prices]))

 

If does not work, please try this code or share your pbix file without sensitive data.

or try this code:

Weighted Pricing Delta =
VAR _s =
    SUMMARIZE (
        'MatHier',
        [Article name],
        "avg", AVERAGE ( 'PREISBAND'[Δ Pricing letter[CUR / UOW]]] ),
        "sum", SUM ( 'OH01 + OS01'[NES 3rd party (€)] )
    )
VAR _1 =
    SUMX ( FILTER ( _s, [avg] <> BLANK () ), [avg] * [sum] )
VAR _2 =
    SUMX ( FILTER ( _s, [avg] <> BLANK () ), [sum] )
RETURN
    DIVIDE ( _1, _2 )

 

Best Regards

Community Support Team _ chenwu zhu

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak
Super User
Super User

@lennardheyder , Try like

 

Weighted Pricing Delta =
VAR __CATEGORY_VALUES = VALUES('MatHier'[Article name])
RETURN
DIVIDE(
SUMX(
FILTERS(__CATEGORY_VALUES, not(isblank(AVERAGE('PREISBAND'[Δ Pricing letter[CUR / UOW]])))),
CALCULATE(
AVERAGE('PREISBAND'[Δ Pricing letter[CUR / UOW]])
* SUM('OH01 + OS01'[NES 3rd party (€)])
)
),
SUMX(
FILTERS(__CATEGORY_VALUES, not(isblank(AVERAGE('PREISBAND'[Δ Pricing letter[CUR / UOW]])))),
CALCULATE(SUM('OH01 + OS01'[NES 3rd party (€)]))
)
)

Snag_3946dcb0.png

 Formula does not work unfortunatley.... 

 

Seems that "Filters" does not work with the input of a variable

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.