Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
How can I build a DAX formula that will return the average of the highlighted column. I'm trying to do this to get the average by year & quarter:
Can you please try this
: AverageX(AllExcept(Table,Year,Quarter),(positivefeedback/Allgroups*100))
* In Place of Table keep your table name.
Thanks,
Aditya
@Anonymous I've tried doing it this way but for whatever reason it comes out to 100% for every month:
Can you please post your calculation and few rows sample data here..
@Anonymous Sure:
And if it makes a difference, I unpivoted my original table to look like this:
So everything in the last 2 columns originally had their own column. I don't know if the unpivot throws this kind of formula off. So positive feedback would be everything that consists of a positive sounding value (strongly agree, agree, happy, very happy).
I think this should work :
avg = CALCULATE(AVERAGEx(zero,Zero[Column1]/Zero[Column2]) ,ALLEXCEPT(Zero,Zero[Quarter],Zero[Year]))
Note : Zero is table name I have created
@Anonymous Still not quite:
That's weird, It is working at my end: Can you please write 2 measure separately
Measure 1 : AVERAGEx(zero,Zero[Column1]/Zero[Column2])
Measure 2:= CALCULATE(AVERAGEx(zero,Zero[Column1]/Zero[Column2]) ,ALLEXCEPT(Zero,Zero[Quarter],Zero[Year]))
and paste your entire column list here...
@Anonymous
Both measures are in the middle. Could this be happening because of the way my table is setup through the unpivot? If AVERAGEX function calculates everything on a row basis, I think that may be why it's returning 100% on each row... But I'm not sure.
I do think Unpivot is causing an issue,I am surprised to what average is not calculating row-wise.
IF we fix that everything else will be done
@Anonymous Gotcha. I will see if I can recreate everything without unpivoting. Thank you for all your help!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |