topic Re: How to add a column/measure to calculate Average in Matrix (or Table)? in DAX Commands and Tips

How to add a column/measure to calculate Average in Matrix (or Table)?

JustinDoh1 — Fri, 10 Jun 2022 19:34:40 GMT

(This is same question that I posted to the "Desktop" yesterday, but I have not gotten any feedback, so I decide to ask in "DAX Commands and Tips".)

I am trying to add a Column here in Matrix that calculates Average of all numbers listed throughout dates:

So, example, it would be (21+21+19+19..+20) / 8 = average for "VA".

Currenly, I only have Total (that comes matrxi as Column subtotals).

This is illustration of Rows, Columns and Values from Pbix file:

What is formula for column? Do I have to use measure for this?

If Matrix is not possible, should I change to Table visual?

Thanks.

Re: How to add a column/measure to calculate Average in Matrix (or Table)?

vapid128 — Fri, 10 Jun 2022 22:57:16 GMT

add measure

DailyClients = AVERAGEX(VALUES([DATE]), CALCULATE([# OF CLINTS]))

Re: How to add a column/measure to calculate Average in Matrix (or Table)?

JustinDoh1 — Fri, 10 Jun 2022 23:09:41 GMT

vapid128 Thank you so much.

How do you put the measure at the end only?

Mine looks like this (repeating):

I was not able to put inside Column directly..

Thanks.

Re: How to add a column/measure to calculate Average in Matrix (or Table)?

vapid128 — Fri, 10 Jun 2022 23:09:32 GMT

remove the (# of Clients) in values

Re: How to add a column/measure to calculate Average in Matrix (or Table)?

JustinDoh1 — Fri, 10 Jun 2022 23:20:16 GMT

vapid128

Thank you so much!

Sorry. one more thing..

Is there way to format number to display with no decimal for each day's value but with one decimal for the average (illustrated as below)?

Re: How to add a column/measure to calculate Average in Matrix (or Table)?

vapid128 — Sat, 11 Jun 2022 00:09:43 GMT

Re: How to add a column/measure to calculate Average in Matrix (or Table)?

JustinDoh1 — Sat, 11 Jun 2022 01:18:13 GMT

vapid128 Thank you so much!

Re: How to add a column/measure to calculate Average in Matrix (or Table)?

JustinDoh1 — Mon, 20 Jun 2022 19:18:09 GMT

vapid128

I am tryng to understand the logic of this measure (decimalFix) here.

How does the "Average" not be considered as

DISTINCTCOUNT(DimDate1[DateFormat])<>1 ?

Thanks.

Re: How to add a column/measure to calculate Average in Matrix (or Table)?

JustinDoh1 — Mon, 20 Jun 2022 19:31:25 GMT

vapid128

Sorry for bothering you again.

I am trying to understand how measure works in PowerBI.

I know this (bottom image) is where it addes column subtotal.

But, what caused this to not display "subtotal", but display "average"?

If I put Column subtotals as "off", no "average" shows up, so I am wondering how does Average tie to "subtotals".

Thanks.