Forum Discussion

Tob_P's avatar
Tob_P
Icon for Helper V rankHelper V
3 years ago
Solved

Greater than 50% calculated column

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

  • tamerj1's avatar
    tamerj1
    3 years ago

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

6 Replies

  • Tob_P's avatar
    Tob_P
    Icon for Helper V rankHelper V
    3 years ago

    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.

  • Tob_P's avatar
    Tob_P
    Icon for Helper V rankHelper V
    3 years ago

    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!

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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] )
  • Tob_P's avatar
    Tob_P
    Icon for Helper V rankHelper V
    3 years ago

    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's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      3 years ago

      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's avatar
        Tob_P
        Icon for Helper V rankHelper V
        3 years ago

        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