Power bi DAX calculate the average of values over a group by column

user_6054545 0
Viewed 126

I have two tables as follow:

enter image description here

the tables are linked to each other using the SRGT key, I want to calculate the average values for each RESPONDENT_SRGT.

Explanation:

the screenshot below represents the inner join of the two tables:

enter image description here

The needed Results:

enter image description here

I just want the average as a mesure so I can calculate the count of RESPONDENT that have an average greater than or equal to 4, for this example, I will get 2 RESPONDENTS.

I was Able to achieve the desired results in SQL server with the bellow query:

(SELECT COUNT(*) FROM

(SELECT AVG(Cast(DIM.VALUE as Float)) AS AVR 
FROM [dbo].[FACT_TABLE] FACT
INNER JOIN [dbo].[DIM_ANSWER_TABLE] DIM
ON FACT.ANSWER_SRGT = DIM.ANSWER_SRGT
GROUP BY FACT.RESPONDENT_SRGT) QRB

WHERE QRB.AVR >= 4  )
dax powerbi
1 Answers

It can be like this:

myMeasure =
VAR myTbl=
    SUMMARIZE(
        'FACT_TABLE'
        ,FACT_TABLE[RESPONDENT_SRGT]
        
    )
VAR myTblWithAvr=
        ADDCOLUMNS(
            myTbl
            ,"avr",CALCULATE(AVERAGE(DIM_ANSWER_TABLE[VALUE]))
        )
VAR filtered =
        FILTER(
            myTblWithAvr
            ,[avr]>=4
        )
RETURN
    COUNTROWS(filtered)
user_190423790
Related