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 ...
Need Help
Tips and Tricks
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