Average measure not working

[HemanthV]

Hello Guys,

I have the data as shown below.

I am showcasing montly count in a matrix visual.

Now i wanted to showcase quarterly average count in another matrix but the measure that i am using is not working as expected

For Q1 the result will be (jan+feb+mar)/3 right then the output will be

Q1
Bad 0.66 (1+1+0)/3

Clear 2 (1+3+2)/3

Good 0.66 (2+0+0)/3
Total 3.33

Measure i have used

Average = AVERAGEX(Table1, DISTINCTCOUNT(Table1[Name]))

Please help me on this issue. Attaching sample pbix for referrence.

https://drive.google.com/file/d/1y9QKbtZLELTVAJKuC9mw7wdEdYh0GW7d/view?usp=sharing

Thanks in advance!

[Anonymous]

Hi @HemanthV ,

Does the replies above solve your problem? If it has been solved, please mark the correct reply as the standard answer to help the other members find it more quickly.Thank you very much for your kind cooperation!

Hope it helps,

Community Support Team _ Caitlyn

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k · Accepted solution]

@HemanthV when you are working with dates, as a best practice, always add a calendar/date dimension in the model, you can use my post to add one Create a basic Date table in your data model for Time Intelligence calculations | PeryTUS IT Solutio...

and see the attached solution with the fix.

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[HemanthV]

Hi @parry2k ,

Thank you so much! It worked!

[HotChilli]

I think you should use a separate Dates table (which holds quarter, monthname, all that good stuff) and a separate status dimension table (just the 3 statuses in it) with appropriate relationships to the fact table.

You can then use the dimension fields in your visuals.

You can then create a couple of measures:

StatTotal = COUNT(Table1[Status]) + 0
and
QuarterAvg = [StatTotal] / 3