Forum Discussion

HemanthV's avatar
HemanthV
Icon for Helper II rankHelper II
4 years ago
Solved

Average measure not working

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!

Need Help

4 Replies

  • HotChilli's avatar
    HotChilli
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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

     

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    4 years ago

    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 Solutions

     

    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.⚡

     

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.