Forum Discussion

youssefm9's avatar
youssefm9
Icon for Helper I rankHelper I
3 years ago
Solved

Calculating The Average in dynamic way

 

Hi All,

I am trying to calculate subtotal average for the below. 

In the below table I have in each row the total MRR (month recurring revenue) for each month. If I want to calculate the MRR per client and per group, I simply divide the MRR by Client or Group. In that way I am calculating the average for each month. When I select in the filter a specific month, the figures will give correct result.

However, if I want to calculate the overall average when I select more than 1 month, I need to sum up all MRR for the selected months and divide by the number of the selected months and then divide by the average number of group or client.

 

Would appreciate your support.

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi  youssefm9 ,

    I created some data:

    Here are the steps you can follow：

    1. Create calculated table.

    Slicer =
DISTINCT('Table'[Month_Year])

    2. Create measure.

    Client_Measure =
var _select=SELECTCOLUMNS('Slicer',"1",[Month_Year])
var _count=COUNTX(ALLSELECTED('Slicer'),[Month_Year])
var _selectsum=SUMX(FILTER(ALL('Table'),'Table'[Month_Year] in _select),[MRR])
return
IF(
    HASONEFILTER('Table'[Month]),
    [Average per Client],
    DIVIDE(
    DIVIDE(
        _selectsum ,_count),    AVERAGEX(FILTER(ALLSELECTED('Table'),'Table'[Month_Year] in _select),[Clients])))
    Group_Measure =
var _select=SELECTCOLUMNS('Slicer',"1",[Month_Year])
var _count=COUNTX(ALLSELECTED('Slicer'),[Month_Year])
var _selectsum=SUMX(FILTER(ALL('Table'),'Table'[Month_Year] in _select),[MRR])
return
IF(
    HASONEFILTER('Table'[Month]),
    [Average per Group],
    DIVIDE(
    DIVIDE(
        _selectsum ,_count),    AVERAGEX(FILTER(ALLSELECTED('Table'),'Table'[Month_Year] in _select),[Groups])))

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

3 Replies