topic Re: Need help with the DAX Calculation in DAX Commands and Tips

Need help with the DAX Calculation

Anonymous — Thu, 04 Jul 2024 18:07:39 GMT

Hi Team,

I have a sample file. (I'm unable to attach the file over here but I can share)

Based on the data, the output generated by the below dax for "Average Lead Time" is 16.

Average Lead Time =
CALCULATE(MAX(Sheet2[LEAD_TIME_NR]),
FILTER(ALLSELECTED(Sheet2[LEAD_TIME_NR]),[1 % Of Arrivals Running Total] >= 0.8 && [1 Previous Cumulative] < 0.8)
)

Other Dax used:

1 % Of Arrivals Running Total =

CALCULATE(

[1 % Of Arrivals],

FILTER(

ALLSELECTED('Sheet2'[LEAD_TIME_NR]),

ISONORAFTER('Sheet2'[LEAD_TIME_NR], MAX('Sheet2'[LEAD_TIME_NR]), DESC)

)

1 Previous Cumulative = [1 % Of Arrivals Running Total] - [1 % Of Arrivals]

1 % Of Arrivals = divide([Stay Number of Arrivals], [1 Total Arrivals])

Stay Number of Arrivals:= CALCULATE(sum(Sheet2[ROOMS_OCC_NR]), FILTER(Sheet2, Sheet2[ARRIVAL_IND] = "Y"))

1 Total Arrivals = CALCULATE(sum(Sheet2[ROOMS_OCC_NR]), all(Sheet2[LEAD_TIME_NR]), Sheet2[ARRIVAL_IND] = "Y")

I tried but unable to break the logic in the dax. Please if someone could help me would be greatly appreciated.

Many Thanks,

Mithilesh

Re: Need help with the DAX Calculation

lbendlin — Fri, 05 Jul 2024 01:04:53 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Need help with the DAX Calculation

Anonymous — Fri, 05 Jul 2024 02:47:00 GMT

Hi lbendlin,

Thank you so much for responding.

I have uploaded the file to the following link.

https://www.dropbox.com/scl/fi/mk1rtyuj5vfi58renianc/Sample-File.xlsx?rlkey=9jfalqlo4ae90urn704ocf7u0&dl=0

Thanks,

Mithilesh

Re: Need help with the DAX Calculation

lbendlin — Fri, 05 Jul 2024 21:50:13 GMT

1 % Of Arrivals = divide([Stay Number of Arrivals], [1 Total Arrivals])

This makes no sense as it will always return 1

What are you trying to achieve ultimately?

Re: Need help with the DAX Calculation

Anonymous — Fri, 05 Jul 2024 21:43:02 GMT

Hi lbendlin,

You're absolutely right, it will always give 1. Actually, this was written by someone a long time back.

I would like to trace how the "Average Lead Time" is 16

Please help me.

Thanks,

Mithilesh

Re: Need help with the DAX Calculation

lbendlin — Fri, 05 Jul 2024 22:35:22 GMT

"Average Lead Time" is neither an average nor will it ever hit 16, at least not based on your sample data.

What is the business problem you are trying to solve?

Re: Need help with the DAX Calculation

Anonymous — Fri, 05 Jul 2024 22:44:24 GMT

I'm trying to find the "Average Lead Time". As per the Power BI file, I created, it shows ""Average Lead Time" as 16. The Power BI file is in the following location

https://www.dropbox.com/scl/fi/mk1rtyuj5vfi58renianc/Sample-File.xlsx?rlkey=9jfalqlo4ae90urn704ocf7u0&dl=0

Thanks,

Mithilesh

Re: Need help with the DAX Calculation

lbendlin — Fri, 05 Jul 2024 22:55:36 GMT

Have you tried a graphical solution? Here is the average lead time based on your raw data, by date, Arrival Indiator and Rooms Occ Nr.

What is the point of the running totals?

Re: Need help with the DAX Calculation

Anonymous — Fri, 05 Jul 2024 23:09:13 GMT

Yes, I did and I'm getting 13.88

However, I'm curious to know how Power BI is returning 16 based on the DAX formula.

I think I'm missing something in the following DAX:

1 % Of Arrivals Running Total =

CALCULATE(

[1 % Of Arrivals],

FILTER(

ALLSELECTED('Sheet2'[LEAD_TIME_NR]),

ISONORAFTER('Sheet2'[LEAD_TIME_NR], MAX('Sheet2'[LEAD_TIME_NR]), DESC)

)

Thanks,

Mithilesh

Re: Need help with the DAX Calculation

Anonymous — Tue, 09 Jul 2024 11:14:26 GMT

Hi lbendlin,

I was able to break down the working of DAX. Basically, it is performing the running totals to get a distribution of lead time with respect to the average lead time. And this is done by the function ISONORAFTER