How to make Aggr work with any Dimension

[liuis]

Hi, community

I've got a table with clients debts looking like this

Customer	Date	Debt
Customer1	01/01/2020	100
Customer2	01/01/2020	10
Customer1	02/01/2020	200
Customer2	02/01/2020	30

I want to make a measure, calculating average debt over dates, and then sum it over customers.

The obvious way is to use Aggr:

My_Measure = sum(aggr(sum([Debt]) / count(distinct Date), Customer))

and it works fine for a table with Customer as a dimension:

Customer	My_Measure
Total	170
Customer1	150
Customer2	20

But if I change the Dimension to [Date], I get the wrong result, since Aggr aggregates Date before it looks for Dimension in Table:

Date	My_Measure	Should be
Total	170	170
01/01/2020	170	110
02/01/2020	0	230

Of course, I can rewrite measure like this

sum(aggr(sum([Debt]) / count(distinct Date), Date, Customer))

but in this case the Total result does not work correctly (it sums to 340)

Is there any workaround, how to make this measure work correctly with Customer OR Date as dimension, and woth correct Totals?

And can I make ot work corectly with any dimensions from linked tables (for example, Month, Year, Customer_Region etc._)

Thanks!

[GaryGiles]

I think you want to use your 2nd expression:

sum(aggr(sum([Debt]) / count(distinct Date), Date, Customer))

If you change the "Totals function" to Avg in the Properties panel for the measure, it will give you the correct total.  It should also work if you use other dimensions.

[sakthi266]

Hi, 

     In that case , the customer based value will show total as 85

Customer	My_Measure
Total	85
Customer1	150
Customer2	20