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 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. 

 

Need Help

3 Replies

  • 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: 

       

       

       

       

    • 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: