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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
117madvillain
New Member

Weighted Average with ALLSELECTED()

Hi,

I'm struggling to compute a weighted average with my dataset. I'm looking to calculate a weighted average for product by sub-region. I also want the calculation to respond to filters applied on the sub-region column (if I take some regions out I still want the weight % to sum to 1). Sample dataset here:

 

117madvillain_1-1675364768317.png

Matrix visual here: I've got the weight percentage to work correctly and used ALLSELECTED() to have the weight respond correctly to any filters applied on the Sub-Region field. However I'm struggling to get the Product subtotal to sum the values below it, instead it just takes the Sum from 2nd value column. 

117madvillain_0-1675364650504.png

See code below for weight:

Weight = 
    DIVIDE(
        SUM('Data LTV by Region'[Ending Customers]),
        CALCULATE(SUM('Data LTV by Region'[Ending Customers]), ALLSELECTED('Data LTV by Region'[Sub-Region])
        ) 
    )

Code for the weighted LTV

LTV Weight = 
    [Weight]*sum('Data LTV by Region'[Est. LTV])

Simpler attempt at getting the weighted average at the Product subtotal 

Product Weighted = 
    IF(
        ISINSCOPE('Data LTV by Region'[Product]),
        SUMX('Data LTV by Region', [LTV Weight]),
        SUM('Data LTV by Region'[Est. LTV])
    )

Tried using SUMMARIZE and writing something a little more complex to return the sum of a weighted average column but admittedly not too experienced with using SUMMARIZE. Code below and underneath is the error I'm getting.

 

LTV Region Weighted Average Table = 
    VAR CustomersEnding =
        SUM('Data LTV by Region'[Ending Customers])
    VAR LTV =
        SUM('Data LTV by Region'[Est. LTV])
    VAR CustomersEndingTotal =
        CALCULATE(CustomersEnding, REMOVEFILTERS('Data LTV by Region'[Sub-Region]))
    VAR Wgt = 
        CALCULATE(DIVIDE(CustomersEnding, CustomersEndingTotal))
    VAR WeightedAvgTable =
        ADDCOLUMNS(
            SUMMARIZE(
            FILTER('Data LTV by Region', ALLSELECTED('Data LTV by Region'[Sub-Region])),
                'Data LTV by Region'[Product], 'Data LTV by Region'[Sub-Region]
            ),
            "Weight", Wgt,
            "LTV", LTV
        )
RETURN
    SUMX(WeightedAvgTable,
        [Weight]*[LTV]
    )

117madvillain_3-1675365305589.png

My end goal would be to put this into a SWITCH() statement since I've figured out all the other parts of the formula and how to get it to work in the matrix visual. This piece has been trickier than I anticipated but feel like I'm inexperienced and missing something simple. Any help or suggestions would be appreciated with figuring this out. 

 

Thanks!

1 REPLY 1
amitchandak
Super User
Super User

@117madvillain , Try like

 

LTV Region Weighted Average Table =
VAR CustomersEnding =
SUM('Data LTV by Region'[Ending Customers])
VAR LTV =
SUM('Data LTV by Region'[Est. LTV])
VAR CustomersEndingTotal =
CALCULATE(CustomersEnding, REMOVEFILTERS('Data LTV by Region'[Sub-Region]))
VAR Wgt =
CALCULATE(DIVIDE(CustomersEnding, CustomersEndingTotal))
VAR WeightedAvgTable =
ADDCOLUMNS(
SUMMARIZE(
calculateTable('Data LTV by Region',, ALLSELECTED('Data LTV by Region'[Sub-Region]))
'Data LTV by Region'[Product], 'Data LTV by Region'[Sub-Region]
),
"Weight", Wgt,
"LTV", LTV
)
RETURN
SUMX(WeightedAvgTable,
[Weight]*[LTV]
)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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