Why my annual average number equals to sum of average number in each month?

[Dooriya101]

Hi All,

I'm encountering a calculation issue that I'm trying to have an average number by month using Total case number divide Total PC number. And Total case number divide Total PC number are calculated based on two different sheet, however I have used third sheet and link them together. 

And you can see from below screenshot that the average Case by PC = 0.09, and it equals to the sum of each month in the bar chart. I'm wondering if there is anything wrong in my calculation. I' think average number should be a middle number in a fluctuation. Thanks!

Raw Data is like: 

PC Number:

Case Number:

Thrid Table:

In PBI:

Total Case Number = COUNT('Case Table'[Case Number])

Total VALUE = SUM('Main Table'[Value])

Total PC = CALCULATE('Main Table'[Total VALUE], 'Main Table'[Key Figure] = "PC")

Avg Case by PC = [Total Case Number]/[Total PC]

[v-yangliu-msft]

Hi  @Dooriya101 ,

Here are the steps you can follow：

1. Create measure.

Avg Case by PC =
var _count=
 COUNTX(
    'Case Number'
,[Case Number ber])
var _sum=
SUMX(
    FILTER(ALLSELECTED('PC Number'),
    'PC Number'[Key Figure]="PC"),[Value])
return
DIVIDE(
    _count,_sum)

2. Result:

Best Regards,

Liu Yang

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

[Dooriya101]

Hi Yang,

Thanks for you sharing! I tried you DAX in my product enviroment and it did worked out as you stated. But I'm actually seeking the average of each month's average number, so I can use the it to do colour setting for columns, which means I need to manully create a baseline for the monthly average number. Thanks!

[Ashish_Mathur]

Hi,

Share data in a format the can be pasted in an MS Excel file.  Also, show the expected result very clearly.

[Dooriya101]

Hi Ashish,

I've copied the raw in another issue, and somehow I get the number that I want but not sure if the calculation logic is correct and have another issue in colour settings with some extra calculation. If you still interest, pls check this:

Conditionally Formatting reflects all bars with sa... - Microsoft Power BI Community

Thanks!

[Ashish_Mathur]

Hi,

I would not be able to help with this one.  My interest is more around calculations/modelling.