How to calculate average per month for bar chart

[siddrow]

Hi

I'm having trouble creating a measure for average per month. So far I have done the below:

1. Created a measure to count rows for my table e.g.  COUNTROWS(table)

2. Created a measure to count rows that are "Completed on Time" e.g. calculate([count rows measure], 'table'[column name]="Completed on Time")+0

3. Created a 3rd measure for division e.g. DIVIDE([Completed on Time measure],([count rows measure]))+0

4. Added my month/year to X-axis

5. Added measure from step 3 into Y-axis

My issue is it is not calculating average for each month e.g. Aug has 2 lines that are both 100%, which makes Aug total 200%, whereas I'd like it to show 100% as the average. I also can't change the type to average.

I basically want to show the average percentage per month of tasks that have the status "Completed on Time"

[v-mdharahman]

Hi @siddrow,

Thanks for reaching out to the Microsoft fabric community forum.

It looks like you are looking for a way to calculate average per month for the bar chart. As @danextian already responded to your query, please go through hsi resppnse and check if it solves your issue.

I would also take a moment to thank @danextian and @Ashish_Mathur, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.

[v-mdharahman]

Hi @siddrow,

As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help.

If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.

If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.
Thank you for your patience and look forward to hearing from you.

[v-mdharahman]

Hi @siddrow,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution so that other community members can find it easily.

Thank you.

[v-mdharahman]

Hi @siddrow,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[danextian]

Hi @siddrow 

It depends on how the average is to be done. If it is by row in the table,

AVERAGEX ( tablename, [your measure] )

This will iterate through eaach row in the tablename and return the average of [your measure] . The above formula can be very slow on large tables due to the iteration.

If you want the measure to be iterated on a distinct values of a specific column:

AVERAGEX ( VALUES ( tablename[column] ), [your measure] )

For multiple columns:

AVERAGEX (
    -- SUMMARIZECOLUMNS groups data by column1, column2, and column3,
    -- then creates a virtual column using [your measure]
    SUMMARIZECOLUMNS (
        tablename[column1],
        tablename[column2],
        tablename[column3],
        "virtual column name", [your measure]
    ),
    -- AVERAGEX iterates over the summarized table and calculates the average of the virtual column, replace "virtual column name" with a different one if desired
    [virtual column name]
)

If this isn't what you're looking for, please provide a workable sample data (not an image), your expected result from the same sample data and your reasoning behind. While it may not be easy to create a sample data, providing solutions does take time as well.

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[siddrow]

Hi, I won't be able to as I have heaps of dataset on there, not just this one.