Ignore slicer in weighted average calculation

[KBIReports]

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 across all States and All Dates. The report contains a slicer Date slicer WeekOf. The weighted average formula works as expected, however, I cannot get the new measure to ignore the WeekOf slicer. Below is formula:

Average of THC weighted by Grams =

VAR __CATEGORY_VALUES = VALUES('AllLab'[Harvest])

RETURN

    DIVIDE(

        SUMX(

            KEEPFILTERS(__CATEGORY_VALUES),

            CALCULATE(AVERAGE('AllLab'[THC]) * SUM('AllLab'[Grams]),ALL(AllLab[WeekOf]),AllLab[Type]="Flower")

        ),

        SUMX(KEEPFILTERS(__CATEGORY_VALUES), CALCULATE(SUM('AllLab'[Grams]),ALL(AllLab[WeekOf]),AllLab[Type]="Flower"))

    )

I need to display the results in a matrix with Strain as the rows. Unfortunately the This Week Avg THC and All Avg THC are the same value which indicates to me that it is not ignoring the slicer. Any help would be much appreciated. 

[Anonymous]

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]

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: 

[KBIReports]

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: