How to use both Avg and sum function in DAX

[Charu]

Hi Everyone,

I have 4columns like below

Col1 : Col2 : Col3 : Col4

0         0       1         0

I want to first sum these 4 columns and then need to get Average.

Please help me how to create DAX formula

In KPI Visual I need to use that Avg measure in the Target Label

[jcarville]

You can unpivot the 4 columns to create 1 column. Then use this new column and change the type to sum or average using the drop down arrow under the values section of the visualisations pane.

[PietroFarias]

Do you want sum Col1 + Col2 + Col3 + Col4 and make a average this sum?

If so, then:

Measure:= AVERAGEX(Tab1;[Col1]+[Col2]+[Col3]+[Col4])

[Charu]

HI @PietroFarias

I tried the measure but I'm getting following error

Error Message:
ORA-00904: "t1"."QTR2_TARGET": invalid identifier. The exception was raised by the IDbCommand interface.

[Anonymous]

@Charu,

Do you connect to Oracle in Power BI Desktop? Could you please post a screenshot about how you apply PietroFarias's DAX in your table?

Regards,
Lydia

[Charu]

Hi @Anonymous

Yeah, I'm connecting oracle database direct query mode and in the advanced options passing SQL statements(many tables join) and getting data.

Sample is 

ID | Code | Tgt 1 | Tgt2 | Tgt3 | Tgt4

1  | 001    | 0       |  0     | 0      | 0

2  | 003    | 1       |  0     | 0      | 0

I need to take sum (tgt1,tgt2,tgt3,tgt4) and then the Avg of the sum(tgt1,tgt2,tgt3,tgt4)

I have created measure as suggest by @PietroFarias

Measure:= AVERAGEX(Tablename;[Tgt1]+[Tgt2]+[Tgt3]+[Tgt4])

[PietroFarias]

Oh! This case, AVERAGEX not is Supported. In measure and query formulas only.

DAX formula compatibility in DirectQuery mode

See the post below, from this forum, if you help.

Forum: Average of Measure in Direct Query mode