Forum Discussion

Kaitra's avatar
Kaitra
Frequent Visitor
6 years ago
Solved

Weighed Average for User Feedback

Dear all,

 

I have read some articles here in regards of the weighed average DAX expression. But I was not able to get the needed solution (or I am just not skilled enough).

My current situtaion is, that I have table (example below).

I have overall 5 questions and the needed WAC needs to be filtered for each supplier.

 

Supplier IDDateSupplierQuestion1Question2Question3Question4Question5
101.08.2020a42234
201.08.2020b23442
301.08.2020c33221
120.08.2020a23445
220.08.2020b1245

3

 

I need to calculate the weighed average for the supplier for each question with this formula:

WAC = (Amount of people who selected 1 * 1) +  (Amount of people who selected 2 * 2) +  (Amount of people who selected 3 * 3) +  (Amount of people who selected 4 * 4) +  (Amount of people who selected 5 * 5) / Total Amount of people

 

--> Example WAC for supplier A for Question 1 = (0*1)+(1*2)+(0*3)+(1*4)+(0*5) / 8   -->   0+2+0+4+0 / 2   -->  6 / 2 =  3

 

I hope I could give you the needed information to help me 🙂

 

Thanks and cheers,

Kai

Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    6 years ago

    Hi Kaitra ,

     

    First try to unpivot your table:

     

    Then create a calculated column for weighted:

     

     

    weighted =
VAR a =
    CALCULATE (
        COUNT ( 'Table'[Supplier] ),
        ALLEXCEPT ( 'Table', 'Table'[Question], 'Table'[Supplier] )
    )
VAR b =
    CALCULATE (
        COUNT ( 'Table'[Supplier] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Question], 'Table'[Supplier] ),
            'Table'[Value] = EARLIER ( 'Table'[Value] )
        )
    )
RETURN
    b / a

     

     

    Last create the measure for WAC:

     

    WAC = SUMX(SUMMARIZE('Table','Table'[Supplier],'Table'[Question],'Table'[Value],'Table'[weighted],"weightedvalue",'Table'[Value]*'Table'[weighted]),[weightedvalue])

     

     

     

    For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/Ec9jvEYkrDBGh-4Ghsieaa4BELptGFAnzBcf7rayyZLQ5A?e=KW2PHI

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    Best Regards,

    Dedmon Dai

     

2 Replies