Totals on columns not correct

DAX
1

Hi

I have been struggling for over a day trying to get the columns totals correct, can anyone help? Thanks

image

image
image546×742 16.4 KB

2

I have tried Data Mentor and cannot get it to work still.

3

Without knowing everything about the model etc. Including the date context in columns and the data model relationships. I’ve guessed it’s the date column.

I tried my best playing around with a few questions.

I also used the new Claude Sonnet 3.5 we’ve added

#https://mentor.enterprisedna.co/threads/dax-measure-optimization-for-original-cost-calculation-65523

Try out a few things in this thread.

And see if my ideas help. Data Mentor has come up with some quite different ways to approach the formula.

Big tip, provide as much information as you possible can into the prompt about every and any context in the calculation. That includes relationships in your model if they are relevant.

Sam

4

Hi @meldon - Do share sample PBIX file for further help.

Thanks
Ankit J

5

Thanks Sam. I am still not able to solve the problem. I can’t share the pbix as it is work related so not sure how to share a sample. Does EDNA offer consultancy with a fee via online that I could share my screen, without sharing the data?

6

This is what I started with:

OC =
VAR _vTable =
SUMMARIZE(
‘FAR w running total’,
‘FAR w running total’[ITEMNO],
‘FAR w running total’[Date From],
‘FAR w running total’[Date To]
)

VAR Original_Cost =
SUMX(Calender,
CALCULATE(
[OC Cumulative Value],
FILTER(
CROSSJOIN(
CALCULATETABLE(
VALUES(Calender[Date]),
Calender[Year Offset] <= 0
),
_vTable
),
Calender[Date] >= ‘FAR w running total’[Date From] &&
Calender[Date] <= ‘FAR w running total’[Date To]
)
)
)

RETURN
Original_Cost

It looks like this:

image

image
image757×698 58.4 KB

7

Hi @meldon - Do check if you can mask the Sensitive data as explained in below video and can create a sample pbix file to check.

Thanks
Ankit J

9

One thing it could possibly be is the OC cumulative total. Have you used ALLSELECTED within that formula? That can always create various issues when re-using it.

It’s a very complex one. The model and calculations have a lot to them.

Is there any way you can break it down into smaller formulas and see the logic play out one by one until you get the full calc? That’s what I would personally do.

10

Hope these samples help.

sample.pbix (129.6 KB)
sample data.xlsx (35.3 KB)

11

Thanks, I will try this. I have also attached a sample of the data and a sample pbix file.

12

Hi @meldon - Based on the sample PBIX file, the issue seems to be related to measure

OC Cumulative Value = CALCULATE(MAXX('FAR w running total','FAR w running total'[CumulativeTotal]))

Here you are taking MAXX of CumulativeTotal, so while calculating Total at Column level (Date Level), it is considering all Items and taking the MAXX value.

Try changing it to SUMX and see if it works. If there is any specific reason to keeping MAXX, then we need to look into alternatives.

Thanks
Ankit J

13

Thanks Ankit. There can be duplicates of itemno, which are not shown in the sample. MAX provides the correct values but the totals do not sum correctly per day.

14

Hi @meldon - Do check if the new measures “OC Cumulative Value New” and
“OC New” created in the attached file works for your scenario.

sample_Ankit1.pbix (128.1 KB)

Thanks
Ankit J

1 Like
16

That works! Thanks. Can I ask you to have a look at just one more measure? I need to use the ‘OC New’ as the basis for a new measure but only needs to sum the items that have a greater than 0 utilisation. I have created the measure ‘OC utilised’, but is not working.
sample data.xlsx (64.0 KB)
sample.pbix (140.9 KB)

image
image642×806 27.2 KB

17

Hi @meldon - As the initial problem is resolved. Will request you to mark this as resolved and a raise a new ticket so other users can also help.

Thanks
Ankit J

18

Ok to close this ticket. Thanks for your help Ankit.