Average Calculation

[cghanta]

I have a table UPS and a column Age

The Power BI has a slicer based on column location

I want to find Average Age from the column Age, ignoring the slicer and display the measure in the report to dispaly the average as a card, irrespective of the slection in the slicer.

The following syntax is working:

CALCULATE(AVERAGE(UPS[Age for Avg]), ALL(UPS))

But, I want to exclude 0 entries in the Age column for the Average calculation, what syntax should I be using?
The following syntax is not working:
CALCULATE(AVERAGE(UPS[Age Avg]), ALL(UPS), FILTER(UPS, UPS[Age for Dual UPS Avg] <> 0))

[mdaatifraza5556 · Accepted solution]

Hi @cghanta 

Can you please try the below DAX.

CALCULATE(
AVERAGE(UPS[Age for Avg]),
ALL(UPS[Location]), 
UPS[Age for Avg] <> 0
)

If this answers your question, kindly mark it as the solution.

[xifeng_L · Accepted solution]

Hi @cghanta 

You also can try below measure:

CALCULATE(AVERAGE(UPS[Age Avg]),FILTER(ALL(UPS), UPS[Age for Dual UPS Avg] <> 0))

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

[cghanta]

Both the solutions from @xifeng_L and @mdaatifraza5556 worked, Thank you both.
I am not sure how to select both the replies as accepted solution

[xifeng_L · Accepted solution]

Hi @cghanta 

You also can try below measure:

CALCULATE(AVERAGE(UPS[Age Avg]),FILTER(ALL(UPS), UPS[Age for Dual UPS Avg] <> 0))

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

[mdaatifraza5556 · Accepted solution]

Hi @cghanta 

Can you please try the below DAX.

CALCULATE(
AVERAGE(UPS[Age for Avg]),
ALL(UPS[Location]), 
UPS[Age for Avg] <> 0
)

If this answers your question, kindly mark it as the solution.

[cghanta]

Hi @mdaatifraza5556,

I have 2 columns on a the table UPS as follows

Location	1stServiceAge	2ndServiceAge
town1	3	0
town2	5	2
town3	6	0
town4	0	0
town5	2	1

How can I calculate (measure) the average age of both columns, ignoring 0's and slicer.  For the above the answer should be 19/6 = 3.17, if all zeros, give 0

Thank you in advance

[cghanta]

Hi @mdaatifraza5556,

I have 2 columns on a the table UPS as follows

Location	1stServiceAge	2ndServiceAge
town1	3	0
town2	5	2
town3	6	0
town4	0	0
town5	2	1

How can I calculate (measure) the average age of both columns, ignoring 0's and slicer.  For the above the answer should be 19/6 = 3.17, if all zeros, give 0

Thank you in advance

[cghanta · Accepted solution]

Both the solutions from @xifeng_L and @mdaatifraza5556 worked, Thank you both.
I am not sure how to select both the replies as accepted solution