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

Calculate data values from measures doesn't match

Dear All,

I am trying to create a formula that calculates the cost on a revenue days to late.

 

To creat this formula I made a few measures:

- Average revenue per day
- Average day's to late
- Total revenue per day to late.

These measures I created:

Average revenue per day = SUMX('Document Data','Document Data'[Amount in]/365)
Average days to late = AVERAGE('Document Data'[Days to late])
 
The above 2 measures work, but on the next one I want to calculate the "cost" of revenue we miss because of late payments:
Total revenue per days to late = SUMX('Document Data',[Average revenue per day]*[Average days to late])
In my opinion this formula should be correct, but it looks like it calculates diferend value's than the measures show.

I have set the values in cards to show the results

Power BI revenue to late.JPG
 
If I make a calculation like my measure I do receive a different amount
Total revenue per days to late = SUMX('Document Data',[Average revenue per day]*[Average days to late])
 
= SUM 7.09k*0.84=5.96k
 
So somehow I might forget something or it might calculate form other customer data (there is more customer data in my sheet).

Could someone help me with this issue?

Thanks!
Ramon
2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User IX
Super User IX

Re: Calculate data values from measures doesn't match

@RNooijen , I need some data to understand the problem. Can you share sample data and sample output in table format?

 

Now the product is always correct at line level means

SUm(A*B), NOT Sum(A)*Sum(B)

 

So in case you have to do Sum(A)*Sum(B) then you need to use context with values or summarize



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

Highlighted
Super User VI
Super User VI

Re: Calculate data values from measures doesn't match

Is your result just the product of your first two measures?

 

Total Revenue to Late = [Average Revenue Per Day] * [Average Days To Late]

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Please mark my post as a solution! Kudos are also appreciated!

Proud to be a Super User!




View solution in original post

4 REPLIES 4
Highlighted
Super User IX
Super User IX

Re: Calculate data values from measures doesn't match

@RNooijen , I need some data to understand the problem. Can you share sample data and sample output in table format?

 

Now the product is always correct at line level means

SUm(A*B), NOT Sum(A)*Sum(B)

 

So in case you have to do Sum(A)*Sum(B) then you need to use context with values or summarize



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

Highlighted
Frequent Visitor

Re: Calculate data values from measures doesn't match

Thanks a lot for your reply,

I don't understand the part of SUM(A*B), NOT Sum(A)*Sum(B)

Below I have pasted a part of the data set I use in table format, is this useful?

Thanks!

   Account    DocumentNo C/IDoc. Date Clearing   DunAmountPayTDaysDays to late
7514908966120774Invoice10/01/2020 00:0024/02/2020 00:00 910045D450
7517447166123082Invoice14/01/2020 00:0009/04/2020 00:00 1446045D8641
7517447166123595Invoice15/01/2020 00:0011/03/2020 00:00 465045D5611
7517447166123596Invoice15/01/2020 00:0011/03/2020 00:00 805445D5611
7517447166168226Invoice16/01/2020 00:0021/02/2020 00:00 91245D36-9
7517447166168264Invoice16/01/2020 00:0008/04/2020 00:00 439545D8338
7517447166168299Invoice16/01/2020 00:0011/03/2020 00:00 5096.545D5510
7514908966128697Invoice21/01/2020 00:0006/03/2020 00:00 67.545D450
7514908966128716Invoice21/01/2020 00:0006/03/2020 00:00 319045D450
7514908966128749Invoice21/01/2020 00:0006/03/2020 00:00 18045D450
7517447166129215Invoice22/01/2020 00:0011/03/2020 00:00 3997.545D494
7517447166129216Invoice22/01/2020 00:0011/03/2020 00:00 6467.545D494
7517447166156268Invoice24/01/2020 00:0024/02/2020 00:00 427445D31-14
7514908966134708Invoice28/01/2020 00:0013/03/2020 00:00 476545D450
7514908966134712Invoice28/01/2020 00:0013/03/2020 00:00 788545D450
7514908966134737Invoice28/01/2020 00:0013/03/2020 00:00 170045D450
7517447166135116Invoice29/01/2020 00:0011/03/2020 00:00 79095.545D42-3
7517447166135117Invoice29/01/2020 00:0011/03/2020 00:00 656545D42-3
7514908966143650Invoice07/02/2020 00:0023/03/2020 00:00 1442045D450
7514908966148667Invoice13/02/2020 00:0030/03/2020 00:00 431545D461
7514908966155581Invoice21/02/2020 00:0008/04/2020 00:00 375545D472
7517447166160226Invoice27/02/2020 00:0011/03/2020 00:00 91245D13-32
7517447166160227Invoice27/02/2020 00:0011/03/2020 00:00 25545D13-32
7517447166189690Invoice07/04/2020 00:0030/04/2020 00:00 32.545D23-22
7514908968630611Invoice05/06/2020 00:0020/07/2020 00:00 1344045D450
7517447168651872Invoice01/07/2020 00:0031/07/2020 00:00 39845D30-15
7517447168651873Invoice01/07/2020 00:0031/07/2020 00:00 92245D30-15
751696325605127Invoice02/01/2020 00:0014/02/2020 00:00 110.1145D43-2
751696325605128Invoice02/01/2020 00:0014/02/2020 00:00 1351.3545D43-2
751696325605129Invoice02/01/2020 00:0014/02/2020 00:00 7230.345D43-2
751603895605186Invoice02/01/2020 00:0028/02/2020 00:00 5030D5727
751603895605187Invoice02/01/2020 00:0028/02/2020 00:00 383.8830D5727
751603895605220Invoice02/01/2020 00:0008/04/2020 00:00 130.430D9767
751696305605654Invoice03/01/2020 00:0018/02/2020 00:00 318.1845D461
751696305605679Invoice03/01/2020 00:0018/02/2020 00:00 818.845D461
751696305605683Invoice03/01/2020 00:0018/02/2020 00:00 1161.4545D461
751696305605684Invoice03/01/2020 00:0018/02/2020 00:00 1517.7845D461
751696305605686Invoice03/01/2020 00:0018/02/2020 00:00 3791.3445D461
751696305605687Invoice03/01/2020 00:0018/02/2020 00:00 3671.5745D461
751696305605688Invoice03/01/2020 00:0018/02/2020 00:00 8858.2245D461
Highlighted
Super User VI
Super User VI

Re: Calculate data values from measures doesn't match

Is your result just the product of your first two measures?

 

Total Revenue to Late = [Average Revenue Per Day] * [Average Days To Late]

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Please mark my post as a solution! Kudos are also appreciated!

Proud to be a Super User!




View solution in original post

Highlighted
Frequent Visitor

Re: Calculate data values from measures doesn't match

@mahoneypat,

That worked, so I think thats the same as @amitchandak meant with =Sum(A)*Sum(B).

 

Thanks a lot for your support!


Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors