Forum Discussion

tomas12344's avatar
tomas12344
Icon for Helper III rankHelper III
2 years ago

calculate average from aggregated values

HI All
Custome want a report, where the average is calcualted from aggregated values not from detail values of the table. 
The report table contains some hierarchies, base on which the data should be aggregated, so that the average is calcualted from these values.
Here some example data

This is the important part, what is not working, avg is calcualted from detail data

In this case, the levels a\a\a-c and the level a\b (in the picture pink marked) are calcualted correctly. the avg on level a\a is as it  should be the sum is 60 and count is 3, so the avg is 20. that is correct. similar for level a\b, sum is 60 and 2 rows, avg is correct 30.

But when collapsing to the a\a level, like bellow, this is correct when calcualting from detail data, but not with aggregated:

in this case (yellow in picture), this is not correct, base on aggregated values. There should be calcualted:
Sum is OK 120, number of cases should be 2 - that is equal to collaped levels a\a and a\b. So here i would need to have calcualted teh average as 120/2=60.
I think i shodl somehow use the Summarize function, but I did not found the correct way. The second thing is, that the results need to be merged within the table to the hierarchies (there is a summary table wich contains hierarchies and some detail sums of the amounts)

Thank you for help.

Need Help
Tips and Tricks

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi  tomas12344 ,

     

    Here are the steps you can follow：

    1. Create measure.

    Avg_Measure =
IF(
    ISINSCOPE('Table'[c]) || ISINSCOPE('Table'[b]),AVERAGEX(
    'Table',[sum]),
        IF(
            ISINSCOPE('Table'[a]),
            DIVIDE(           SUMX(FILTER(ALL('Table'),'Table'[date]=MAX('Table'[date])&&'Table'[a]=MAX('Table'[a])),[sum])  CALCULATE(DISTINCTCOUNT('Table'[b]),FILTER(ALL('Table'),'Table'[date]=MAX('Table'[date])))),
            IF(
                ISINSCOPE('Table'[date]),
              DIVIDE(
            SUMX(FILTER(ALL('Table'),'Table'[date]=MAX('Table'[date])),[sum]),            CALCULATE(DISTINCTCOUNT('Table'[a]),FILTER(ALL('Table'),'Table'[date]=MAX('Table'[date])))),
            DIVIDE(
            SUMX(ALL('Table'),[sum]),
            CALCULATE(DISTINCTCOUNT('Table'[date]),ALL('Table'))))))

    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

  • tomas12344's avatar
    tomas12344
    Icon for Helper III rankHelper III
    2 years ago

    HI amitchandak Anonymous 

    Thank you for the reply, but I'm not sure, that the results are correct. I have little bit experimented, and found out, that when I use two Summarize function, it will provide correct avg calcualtion. 

    Currently, I dont know, how to connect this together, into the first matrix table( I mean the first table in the picture and the third, which shows correct avg values, as the user wants).