Greater than 50% calculated column

[Tob_P]

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=10...

[Tob_P]

@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

[tamerj1]

@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] )

[Tob_P]

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

[Tob_P]

@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!

[Tob_P]

@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.

[tamerj1 · Accepted solution]

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] )