Average value of percentage at different dimension levels

[manalla]

Hi,

I have belwo scenairo where ValuePercentage_Calc field should show Avgerage value at ClientName level. Image show what i intend to do.

Data source is SSAS Tabular live connection. ValueOne and ValueTwo are also calculated measures at PBI desktop based on many other numeric fields.

ValuePercentage_Calc = CALCULATE(sum(TestTable[ValueOne])/sum(TestTable[ValueTwo]))

Thanks

Manoj

[DataZoe · Accepted solution]

@manalla ,

Please try this:

ValuePercentage_Calc = averagex(values(TestTable[Project]),CALCULATE(sum(TestTable[ValueOne])/sum(TestTable[ValueTwo])))

edit: oops looks like the client name is the xyz, should be project level (proj 1, proj 2) in the above measure.

[amitchandak · Accepted solution]

@manalla , Try like

averageX(Summarize(Table,Table[client Name], Table[Project name],"_1",CALCULATE(sum(TestTable[ValueOne])/sum(TestTable[ValueTwo]))),[_1])

[manalla]

Hi @amitchandak 

Thanks for reply, in my case ValueOne and ValueTwo are already calculted columns based on more than 10 different calculations. So i am not able to use SUM(). How else can we achieve? As table has many other columns (dimension levels), can we include all level in Summarize?

Thanks

Manoj

[DataZoe · Accepted solution]

@manalla ,

Please try this:

ValuePercentage_Calc = averagex(values(TestTable[Project]),CALCULATE(sum(TestTable[ValueOne])/sum(TestTable[ValueTwo])))

edit: oops looks like the client name is the xyz, should be project level (proj 1, proj 2) in the above measure.

[manalla]

@DataZoe , It worked on my dataset. Thank you!