topic Re: Greater than 50% calculated column in DAX Commands and Tips

Greater than 50% calculated column

Tob_P — Wed, 22 Feb 2023 08:53:37 GMT

I have two directly related tables, Purchase Header & Purchase Line with direct relationship between the two based on two common columns (Delivery No).

I want to calculate the number of lines where the delivery is greater than 50%. So the Header has the Delivery No, and the Line table has the Delivery No split out across either a single or multiple line. If there are 7 deliveries that are counted as Day 1 (value in Delivery Day column in Line Table), and 4 are on Day 1 and 3 are on Day 2, then return me the value 'Day 1'. If there is just one Delivery and it's Day 2, then return Day 2...essentially, anything that's > 50%.

In the even that nothing calulates to over 50%, and it's a tie, then the earlier of the 2 days should be in the expected result.

Here is some sample data with expected results - https://docs.google.com/spreadsheets/d/1jYb0CftenoURkr-Oeay2102xtiOyfbdU/edit?usp=share_link&ouid=108389835693298469342&rtpof=true&sd=true

Re: Greater than 50% calculated column

tamerj1 — Wed, 22 Feb 2023 09:52:57 GMT

hI Tob_P
Please refer to attached sample file the solution

Expected Output = VAR CurrentDeliveryTable = CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Delivery No] ) ) VAR SummaryTable = GROUPBY ( currentDeliveryTable, 'Table'[Delivery Day], "@Count", COUNTX ( CURRENTGROUP ( ), 1 ) ) VAR MaxCountTable = TOPN ( 1, SummaryTable, [@Count] ) RETURN MINX ( MaxCountTable, [Delivery Day] )

Re: Greater than 50% calculated column

Tob_P — Wed, 22 Feb 2023 15:57:48 GMT

tamerj1

Thanks for replying! I don't have full access to the tables that I'm using in my .pbix file currently and once I do and run this against it, I'll come back and hopefully mark this as a solution, but in the meantime, just wanted to say thank you for having a go at this for me - appreciated.

Re: Greater than 50% calculated column

Tob_P — Tue, 28 Feb 2023 10:45:07 GMT

tamerj1

Have managed to finally get back into my system to check this out and it works absolutely perfectly! Just wanted to say a massive thank you as had really struggled with this one, and it's great you took the time to solve it for me. Really appreciated!

Re: Greater than 50% calculated column

Tob_P — Mon, 15 May 2023 12:03:06 GMT

tamerj1You had previously helped me out with this - I was looking to amend it so that the criteria would no longer be >50%, but would be >10% - I've been trying to unpick the logic and have been unable to - is it even possible to amend to include the new criteria?

Thanks

Re: Greater than 50% calculated column

tamerj1 — Mon, 15 May 2023 12:55:33 GMT

Tob_P

Please try

Expected Output =
VAR CurrentDeliveryTable =
CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Delivery No] ) )
VAR SummaryTable =
GROUPBY (
currentDeliveryTable,
'Table'[Delivery Day],
"@Count", COUNTX ( CURRENTGROUP (), 1 )
)
VAR MaxCountTable =
TOPN ( 1, SummaryTable, [@Count], ASC )
RETURN
MINX ( MaxCountTable, [Delivery Day] )

Re: Greater than 50% calculated column

Tob_P — Thu, 25 May 2023 11:06:29 GMT

Hi tamerj1

Apologies for delayed response as I was off sick. I hate to ask (I'm only doing so as I am struggling to understand how this piece of DAX works completely, otherwise I would look to amend it myself), and also appreciate you have invested time in this alongside all the other help you offer the community.

I've had a request in order that colleagues would also like to see the results if the following criteria were met (in this order)

If there is a late load (Delivery Day = 4), then the load is counted as Late

If there is a Day 1 load (Delivery Day = 1), then the load is counted as Day 1

If there is a Day 2 load (Delivery Day = 2), then the load is counted as Day 2

If there is a Day 3 load (Delivery Day = 3), then the load is counted as Day 3

Thanks