cancel
Showing results for
Did you mean:
Regular Visitor

## Sum Monthly Divides by Detail

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV

@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])

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

4 REPLIES 4
Super User IV

@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])

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Super User III

Hi,

You may download my PBI file from here.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Regular Visitor

Thanks @amitchandak

it works

Regular Visitor

Thanks @Ashish_Mathur , let me check this solution too

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Power Platform October Community Highlights

Check out the top community contributors across all of the communities

#### Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors