cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

Sum Monthly Divides by Detail

Hello everyone,

I have the following example data

 

MonthDay TermAmount
Sep7100
Sep30120
Sep45104
Sep799
Sep3093
Sep45104
Oct794
Oct30104
Oct45106
Oct797
Oct3090
Oct45106
Nov7105
Nov30104
Nov45101
Nov794
Nov3091
Nov4597

 

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)

MonthDay TermAmount%Avg Day 
Sep71990.3209677422.246774194
Sep302130.34354838710.30645161
Sep452080.33548387115.09677419
 Total Month620 27.65

 

 

MonthDay TermAmount%Avg Day 
Oct7191                   0.322.239530988
Oct30194                   0.329.748743719
Oct45212                   0.3615.9798995
 Total Month597 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
javr_0-1603927121220.png

 

 
But when i display only Month and Avg Day Term the sum is not what expected (Sep = 27.65, Oct=27.98)
javr_1-1603927121222.png
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
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!

View solution in original post

4 REPLIES 4
Super User IV
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!

View solution in original post

Super User III
Super User III

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thanks @amitchandak 

it works

 

Detail_2.PNG

Thanks @Ashish_Mathur , let me check this solution too

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

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