Sum Monthly Divides by Detail

[javr]

Hello everyone,

I have the following example data

Month	Day Term	Amount
Sep	7	100
Sep	30	120
Sep	45	104
Sep	7	99
Sep	30	93
Sep	45	104
Oct	7	94
Oct	30	104
Oct	45	106
Oct	7	97
Oct	30	90
Oct	45	106
Nov	7	105
Nov	30	104
Nov	45	101
Nov	7	94
Nov	30	91
Nov	45	97

I have group by Month and Day Term and summarize Amount and  also get Total Amount by Month.

Column % is getting by divide every Amouny line by Total Month, 

Column Avg Day is % * Day Term

I need to get sum go Avg Day (Sep = 27.65, Oct=27.98)

Month	Day Term	Amount	%	Avg Day
Sep	7	199	0.320967742	2.246774194
Sep	30	213	0.343548387	10.30645161
Sep	45	208	0.335483871	15.09677419
	Total Month	620		27.65

Month	Day Term	Amount	%	Avg Day
Oct	7	191	0.32	2.239530988
Oct	30	194	0.32	9.748743719
Oct	45	212	0.36	15.9798995
	Total Month	597		27.9781742

In Power BI I have the next Measures

Measures Option 1

Total Day Term = CALCULATE(SUM(Data[Amount]),ALLEXCEPT(Data,Data[Month], Data[Day Term]))

Total Month = CALCULATE(SUM(Data[Amount]),ALLEXCEPT(Data,Data[Month]))

Min Days Term = MIN(Data[Day Term])

% = DIVIDE([Total Day Term], [Total Month])

Avg Day Term = [Min Days Term] * Data[%]

I got this on Power BI, Avg Day Term is calculared correct at detail

But when i display only Month and Avg Day Term the sum is not what expected (Sep = 27.65, Oct=27.98)

Measure Option 2

Avg Day Term 3 =

CALCULATE( DIVIDE( CALCULATE( SUM(Data[Amount]), ALLEXCEPT(Data,Data[Month], Data[Day Term])),                       

                                  CALCULATE(SUM(Data[Amount]),ALLEXCEPT(Data,Data[Month])))

                    * CALCULATE(MIN(Data[Day Term]),ALLEXCEPT(Data,Data[Month], Data[Day Term]))

         , ALLEXCEPT(Data,Data[Month], Data[Day Term]))

But I get the same result, What Do I missing?

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[javr]

Thanks @Ashish_Mathur , let me check this solution too

[amitchandak · Accepted solution]

@javr , Try daily ratio like

New column

divide([Term Amount],sumx(filter(Table, [month]= earlier([month])),[Term Amount]))

new Measure
% = divide(sum(Data[Term Amount]),sumx(filter(allselected(Data), Data[month]= max(Data[month])),Data[Term Amount]))

Min Days Term = MIN(Data[Day Term])

Avg Day Term = sumx(summarize(Data,Data[Month], Data[Day Term],"_1", [Min Days Term] * [%]),[_1])

[javr]

Thanks @amitchandak 

it works