Forum Discussion
Average based on multiple columns (categories)
Hi there,
In SQL we can do
Create table A (
Company int null,
Job int null,
Phase int null,
Units int null
)
Insert into A (Company,Job,Phase,Units) Values
(01,161705,16109,23),
(01,161705,16109,37),
(01,161705,16109,65),
(16,161705,16109,40),
(16,161705,16109,95),
(16,161705,16109,83),
(01,161705,16110,510),
(01,161705,16110,711),
(01,161705,16110,212),
(16,161705,16110,101),
(16,161705,16110,111),
(16,161705,16110,121)
select Company,
Job,
Phase,
Units,
RN = ROW_NUMBER() over (Partition by Company,Job,Phase Order by Company,Job,Phase),
UnitsAvg = AVG(Units) over (Partition by Company,Job,Phase Order by Company,Job,Phase)
from A
How we can do that in Power BI using DAX Function (with / without grouping the table)
Thanks,
Ed Dror
3 Replies
Hi Anonymous ,
For your requirement, please create the calculated column in Power BI with the formula below.
Column = CALCULATE ( AVERAGE ( Table1[Units] ), ALLEXCEPT ( Table1, 'Table1'[Company], 'Table1'[Phase] ) )Here is your desired output.
Best Regards,
Cherry
Cherry,
It Didn't work I tried AverageX, Calculate measure, yours solution but nothing seems to work
Basiclly (see attachment) I want to repeat the Average (Mean and Median) to all rows based on Phase value
The Total Mean at the bottom 8.29 to show on each and every row So I can do the chart looks like this
The filter based on Phase only not Copany Job Phase.
Thanks,
Oded Dror
