Wrong Total in matrix column

DAX
1

Hello EDNA - reaching out for help w/ an incorrect column total. expecting approx. 280 days , in the AgingLD column(far right), instead of 48K.

PBI file attached.
ProTDVaAgingLFDs.pbix (918.6 KB)

Amount Wrong:

image

Expected Total:

image

Thank you in advance,

Frankee.

2

Goodeving @Frankee ,

Looking at the table outcomes, the total is calculated exactly the same way as the individual lines.

image

TV-5356: 116 / 172.340 * 116,65 = 0,08
Total 172.340 / 172.340 * 48.894,88 = 48.894,88

This way of total calculation is caused by using Sumx in DAging(Max) and AgingL, which forces row by row calculation, adding up into Total, and/but using Max in DAgingD(Sum), which is constant, also in the Total.
In other words, the Total for AgingLD is consistently calculated,
Regrettable it is not possible to sum a measure( AgingLD adding up to 279,44); as Sum requires a column-name. Adjust the calculation ?
Kind regards, Delta Select

3

Hello @Frankee

Did the responses above help solve your query?

If not, can you let us know where you’re stuck and what additional assistance you need?

If it did, please mark the answer as the SOLUTION by clicking the three dots beside Reply and then tick the check box beside SOLUTION

Thank you

4

not what I was expecting as a solution.

5

Hi @Frankee,

Can you let us know where you’re stuck and what additional assistance you need?

6

Hi @Frankee

We’ve noticed that no response was received from you on the post above.

Just following up if you still require assistance to solve your inquiry.

In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

7

what other recommendations would you have. I have the columns that I have. would I have to add a monthly column to the fact table…yr-mo. ?

here’s a simple version in excel of the weighted avg required.

Weighted Average.xlsx (205.6 KB)

Thank you guys again for the help,

Frankee

8

Goodevening @Frankee ,

An alternative approach below, presumably you are looking for inventory aging.
ProTDVaAgingLFDs (v3).pbix (919.8 KB)

Workout:

  1. Using your LAging, renamed to Inventory, using your DAgingD(Max), renamed to Duration
  2. Added Weighted Duration, multiplying Inventory with Duration on a row level with SumX
  3. Added Aging, which divide Weighted Duration by Inventory (integrating the last two measures into one gives again wrong Aging for Totals.)

P.S.

  1. It differs from your initial Excel expectation of 279,44, however the presented approach in my opinion is correct, at least logic (this gives an average aging of 387,35).

The outcome when selecting the products TV-5354 and TV-5355:

image

Hopes this helps you further,
kind regards, Delta Select

9

Thank you so much for that detailed answer @deltaselect

We hope this helped you @Frankee,

If not, how far did you get and what kind of help do you need further?

If yes, kindly mark as the solution the answer that solved your query.

10

@deltaselect - WOW thank you for this, I’ll spend this afternoon reviewing this. I’ll get back to you.

Looks good doe.

Frankee.

11

@deltaselect . Again, appreciate the help. w/ continued support here’s the solution:

AgingLD = SUMX ( VALUES ( TD[TDate] ), [DAgingD%]*[AgingL] )

image
image728×526 47 KB

Thank you again appreciated the guidance,

Frankee.

12

@Frankee , glad to hear that I could someway be of help.
Just for my information, never seen this kind of aging, what does (for example) the red 4 tells you ?

KR DS

13

@deltaselect - with the “4”, I’m only pointing out one example of the total weighted avg. for one [TNo]. It will help customer audit the numbers.

Measure [AgingLD2], has the correct total.
Although [AgingLD] has the same individual #'s , per Transaction Date, but the total is way off.

.

In your opinion if thats not the correct way to capture the weighted avg. Please share your thoughts of how the pcs daily weighted avg should be calculated.

The spread sheet came from customer. I just followed their logic, which could be flawed.

Here’s what the column labels mean.
[AgingLD] = Aging Line Days (incorect total)
[AgingLD2] = Aging Line Days (Correct total)
[TNo] = unique rental # .
[QtyOut] = quantity of pcs (Lines) out on rent per TNo.
[TDate] = Transaction Date , when pcs go out on rent or are returned…where they’re no longer on rent.

[EffDDate] = effective Date off rent or returned.

Thank you again,

Frankee

14

@Frankee , thank you for the response. And it is known that the total of measures not always count correctly, you have solved that issue. See also Fix Incorrect Totals of Greg
As an outsider, I approached it as avg duration of outstanding inventory, which appeared to be Not brought back rented out material. After your clarification, I should expect something like rented out period in days (duration) per transaction per rental product per customer, whereby using the reporting date as end period when rentals not yet brought back, which could be averaged.
Anyway, if the report now suits your information needs, case resolved.