Forum Discussion

atziovara's avatar
atziovara
Icon for Helper I rankHelper I
2 years ago

Weighted Average formula issue

  • I want to compute the Weighted Average for the following set of data:

 

Table Name: TableBI

WaveCompany IDWeightsQuestionsAnswersWeighted AnswersPeriods
110,006Demand change compared to the previous 6 months-1-0,0062022 H2
110,006Expected change in demand in the next 6 months10,0062023 H1
110,006Liquidity problems intensity10,0062022 H2
110,006Expected change in employment002023 H1
120,001Demand change compared to the previous 6 months10,0012022 H2
120,001Expected change in demand in the next 6 months10,0012023 H1
120,001Liquidity problems intensity-1-0,0012022 H2
120,001Expected change in employment10,0012023 H1
130,0057Demand change compared to the previous 6 months10,00572022 H2
130,0057Expected change in demand in the next 6 months002023 H1
130,0057Liquidity problems intensity002022 H2
130,0057Expected change in employment002023 H1
210,006Demand change compared to the previous 6 months10,0062023 H1
210,006Expected change in demand in the next 6 months002023 H2
210,006Liquidity problems intensity002023 H1
210,006Expected change in employment002023 H2
220,001Demand change compared to the previous 6 months10,0012023 H1
220,001Expected change in demand in the next 6 months10,0012023 H2
220,001Liquidity problems intensity-1-0,0012023 H1
220,001Expected change in employment10,0012023 H2
230,0057Demand change compared to the previous 6 months10,00572023 H1
230,0057Expected change in demand in the next 6 months002023 H2
230,0057Liquidity problems intensity-1-0,00572023 H1
230,0057Expected change in employment10,00572023 H2

*Weighted Answers is calculated as: Weights * Answers

**In the column "Answers" -1 denotes decrease (or zero problems for Liquidity problems intensity), 0 denotes stability (or low to medium intensity problems), and 1 denotes increase (or high intensity problems).

 

  • I created the following measure:
Weighted Average =
VAR SelectedChar = SELECTEDVALUE(TableBI[Questions])
VAR SelectedPrice = SELECTEDVALUE(TableBI[Answers])
VAR Numerator =
SUMX(
FILTER(
'TableBI',
NOT(ISBLANK('TableBI'[Weighted Answers])) &&
NOT(ISBLANK('TableBI'[Weights])) &&
'TableBI'[Questions] = SelectedChar &&
'TableBI'[Answers] = SelectedPrice
),
IF(
'TableBI'[Answers] = -1,
('TableBI'[Weighted Answers] * (-1)),
IF(
'TableBI'[Answers] = 0,
'TableBI'[Weights],
'TableBI'[Weighted Answers]
)
)
)
VAR Denominator =
SUMX(
FILTER(
'TableBI',
NOT(ISBLANK('TableBI'[Weighted Answers])) &&
NOT(ISBLANK('TableBI'[Weights])) &&
'TableBI'[Questions] = SelectedChar
),
'TableBI'[Weights]
)
RETURN
DIVIDE(Numerator, Denominator)

 

  • More specifically, when Answer = -1 I want the first SUMX to be calculated for each row of Weighted Answers myltiplied by -1 (for which the filters are true of course), when Answer = 0 I want the first SUMX to be calculated for each row of Weights, and when Answer = 1, I want the first SUMX to be computed for each row of Weighted Answers. 
  • The problem that I am encountering is that I only receive 1 as an answer for all Waves whether I select 0, 1, or -1 as an Answer on my slicer. It seems that for some reason the Numerator and the Denominator are the same, so one of the filters is not being applied.

 

Need Help

4 Replies

  • 123abc's avatar
    123abc
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Here's a modified version of your DAX measure:

     

    Weighted Average =
    VAR SelectedChar = SELECTEDVALUE(TableBI[Questions])
    VAR SelectedPrice = SELECTEDVALUE(TableBI[Answers])

    VAR Numerator =
    SUMX(
    FILTER(
    TableBI,
    NOT(ISBLANK(TableBI[Weighted Answers])) &&
    NOT(ISBLANK(TableBI[Weights])) &&
    TableBI[Questions] = SelectedChar &&
    TableBI[Answers] = SelectedPrice
    ),
    IF(
    SelectedPrice = -1,
    TableBI[Weighted Answers] * -1,
    IF(
    SelectedPrice = 0,
    TableBI[Weights],
    TableBI[Weighted Answers]
    )
    )
    )

    VAR Denominator =
    SUMX(
    FILTER(
    TableBI,
    NOT(ISBLANK(TableBI[Weighted Answers])) &&
    NOT(ISBLANK(TableBI[Weights])) &&
    TableBI[Questions] = SelectedChar
    ),
    TableBI[Weights]
    )

    RETURN
    DIVIDE(Numerator, Denominator)

    • atziovara's avatar
      atziovara
      Icon for Helper I rankHelper I
      2 years ago

      123abc 
      Thank you for your answer!


      I am afraid the modified version that you sent me is still not working. I am now receiving 1 for all waves when Answer = 0 or 1, and -1 when Answer = -1.


      It seems like the rows of the Numerator are not filtered correctly, and the Numerator is the same as the Denominator.

      • FreemanZ's avatar
        FreemanZ
        Icon for Super User rankSuper User
        2 years ago

        hi atziovara ,

         

        not sure if i fully get you, try like:

        1) feed the slicers with isolated calculated tables like:

        answers = VALUES(TableBI[Answers])
questions = VALUES(TableBI[Questions])


        2) plot a visual table with a slightly modified measure based on yours like:

        Weighted Average = 
VAR SelectedChar = SELECTEDVALUE(Questions[Questions])
VAR SelectedPrice = SELECTEDVALUE(Answers[Answers])
VAR Numerator =
SUMX(
    FILTER(
        'TableBI',
        NOT(ISBLANK('TableBI'[Weighted Answers])) 
            &&NOT(ISBLANK('TableBI'[Weights])) 
            &&'TableBI'[Questions] = SelectedChar 
            &&'TableBI'[Answers] = SelectedPrice
    ),
    IF(
        'TableBI'[Answers] = -1,
        ('TableBI'[Weighted Answers] * (-1)),
        IF(
            'TableBI'[Answers] = 0,
            'TableBI'[Weights],
            'TableBI'[Weighted Answers]
        )
    )
)
VAR Denominator =
SUMX(
    FILTER(
        'TableBI',
        NOT(ISBLANK('TableBI'[Weighted Answers])) 
            &&NOT(ISBLANK('TableBI'[Weights])) 
            &&'TableBI'[Questions] = SelectedChar
    ),
    'TableBI'[Weights]
)
RETURN DIVIDE(Numerator, Denominator)


        it worked like: