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