Forum Discussion

KBIReports's avatar
KBIReports
Frequent Visitor
2 years ago

Ignore slicer in weighted average calculation

Hello,   I need to create a weighted average measure in a matrix that ignores a slicer on the page. The matrix should contain Wtd Avg THC by Strain for a specific date and a WTD Avg THC by Strain a...
Need Help
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi KBIReports ,

 

Try to modify your formula like below:

1 = 
VAR __CATEGORY_VALUES =
    VALUES ( 'AllLab'[Harvest] )
RETURN
    DIVIDE (
        SUMX (
            __CATEGORY_VALUES,
            CALCULATE (
                AVERAGE ( 'AllLab'[THC] ) * SUM ( 'AllLab'[Grams] ),
                REMOVEFILTERS ( 'AllLab'[WeekOf] ),
                'AllLab'[Type] = "Flower"
            )
        ),
        SUMX (
            __CATEGORY_VALUES,
            CALCULATE (
                SUM ( 'AllLab'[Grams] ),
                REMOVEFILTERS ( 'AllLab'[WeekOf] ),
                'AllLab'[Type] = "Flower"
            )
        )
    )

Best Regards,
Adamk Kong

 

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

 

KBIReports's avatar
KBIReports
Frequent Visitor
2 years ago

Hi @v-kongfanf-msft. I used your suggested formula. The first strain displayed the weighted average ignoring the slicer but the second strain is not ignoring the week selected. Below are images of the data: