Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
amitchandak
Super User
Super User

@Anonymous , 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

View solution in original post

mahoneypat
Employee
Employee

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? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

4 REPLIES 4
mahoneypat
Employee
Employee

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? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Anonymous
Not applicable

@mahoneypat,

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

 

Thanks a lot for your support!


amitchandak
Super User
Super User

@Anonymous , 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

Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.