Average based on multiple columns (categories)

[Anonymous]

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

[v-piga-msft]

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

[Anonymous]

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

[Anonymous · Accepted solution]

Cherry,

I solved it by adding Summarize table with two columns 1 = Group by Phase 2 = Average(Projected UnitHour)
Then established relationship on Phase with bi directional and it works

Thanks,
Ed Dror