topic Re: Average Calculation in DAX Commands and Tips

Average Calculation

cghanta — Mon, 24 Mar 2025 02:29:17 GMT

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))

Re: Average Calculation

mdaatifraza5556 — Mon, 24 Mar 2025 07:09:37 GMT

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.

Re: Average Calculation

xifeng_L — Mon, 24 Mar 2025 08:21:41 GMT

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~

Re: Average Calculation

cghanta — Mon, 24 Mar 2025 11:17:35 GMT

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

Re: Average Calculation

cghanta — Mon, 24 Mar 2025 11:19:25 GMT

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

Re: Average Calculation

cghanta — Sat, 29 Mar 2025 01:06:28 GMT

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

Re: Average Calculation

cghanta — Sat, 29 Mar 2025 01:06:39 GMT

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