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 Wave Company ID Weights Questions Answers Weighted Answers Periods 1 1 0,006 Demand cha...
Need Help
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:

     

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

      FreemanZ 
      Thank you very much for your response! 

      This partially resolves the issue! However, I have two more questions related to your answer:

       

      (1) Some of the Answers of the original data do not have -1, 0, or 1 as values. They can take values varying from -100 to 100. Therefore, when I select a specific question in the relevant slicer of tables Question, the slicer with the Answers showcases all possible values and not the ones that are associated with the question I selected. 

      Please note that it would not be preferable to just filter the Answers slicer, in case in the future the Weighted Average is expanded with more IF statements. I would like to be able to see the Answers that relate to the specific Question that is selected.

      Is there a way to fix this?

       

      (2) Also, if I want to compare all 3 of the Answers to a Question in one graph, would I need to make 2 more disconnected tables with 2 new measures (which will have the same formula as [Weighted Average] though), plus 2 more disconnected tables which will have Questions as their only column, and 2 more disconnected tables which will have Answers as their column?

      Thank you very much in advance!