locked
How to make calculations WITHIN a category RRS feed

  • Question

  • How do I make calculations that are contained WITHIN the category of each claim.

    I want to use these rules but WITHIN the category of each claim.

    1)  If Type= Dispatch and Status = Yes, that is the pricing agreement for the Claim.

    2) If type= dispatch and status = NO, then if there is a redispatch with status= YES, use that pricing agreement for the claim.

    3) All else return blank

    Yellow is the output that I would like. 

    Spreadsheet attached

    https://www.dropbox.com/s/qu67vikccr1j7r1/Book3.xlsx?dl=0

    Thursday, January 5, 2017 7:34 PM

Answers


  • Hi Albo44,

    Ok, I understand it. First we should create a rank column, we will look for by first(Dispatch)->second(Redispatch)->third(Cashout) order for each claim category.

    Rank=RANKX(FILTER(Table1,Table1[Claim]=EARLIER(Table1[Claim])),Table1[Work Order],,ASC,Dense)

    Then create a calculated column named “First” to recognize Type= Dispatch and Status = Yes. “Second” column display type= dispatch and status = NO.

    First=IF(AND(AND(Table1[Type]="Dispatch",Table1[Status]="Yes"),Table1[Rank]=1),1,0)

    Second=IF(AND(Table1[Type]="Redispatch",Table1[Status]="Yes"),1,0)



    Finally, create a “Resuat_label” column to calculate the pricing agreement for the Claim like the following screenshot. The rows including value is 1  in  “Resuat_label” are equal to the yellow you highlighted.  

    Label=IF(AND(AND(Table1[Type]="Dispatch",Table1[Status]="Yes"),Table1[Rank]=1),1,IF(AND(Table1[Type]="Redispatch",Table1[Status]="Yes"),1,0))
    
    Result_label=IF(CALCULATE(SUM(Table1[Label]),ALLEXCEPT(Table1,Table1[Claim]))=1,Table1[Label],Table1[First])



    Create a measure and pivot table show what you want.

    measure:=CALCULATE(COUNTA(Table1[Claim]),FILTER(Table1,Table1[Result_label]=1))

      



    If you have any other issue, please feel free to ask.

    Best Regards,
    Angelia


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Friday, January 6, 2017 3:39 AM

All replies

  • Hi,

    You can express this type of business logic within a DAX CALCULATE statement.


    Kind Regards,

    Kieran.


    Kieran Patrick Wood http://www.innovativebusinessintelligence.com http://uk.linkedin.com/in/kieranpatrickwood http://kieranwood.wordpress.com/

    Friday, January 6, 2017 1:12 AM
  • Hi Kieran, 

    I don't believe a simple calculate statement will work for what I'm trying to get to. 

    Friday, January 6, 2017 1:24 AM
  • Is there any chance of you sharing the relevant tables and relationships within your data model?

    Kieran Patrick Wood http://www.innovativebusinessintelligence.com http://uk.linkedin.com/in/kieranpatrickwood http://kieranwood.wordpress.com/

    Friday, January 6, 2017 1:30 AM
  • Hi Albo44,

    The highlighted in yellow is conflicting with your description. There is Type= Dispatch and status = No for Uni, and there is a Type= Redispatch with status= Yes for Uni, why is the Uni not counted and highlighted in yellow? Based your description, there should be 2 Uni. Please share more details and describe clearly.


    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, January 6, 2017 1:54 AM

  • Hi Albo44,

    Ok, I understand it. First we should create a rank column, we will look for by first(Dispatch)->second(Redispatch)->third(Cashout) order for each claim category.

    Rank=RANKX(FILTER(Table1,Table1[Claim]=EARLIER(Table1[Claim])),Table1[Work Order],,ASC,Dense)

    Then create a calculated column named “First” to recognize Type= Dispatch and Status = Yes. “Second” column display type= dispatch and status = NO.

    First=IF(AND(AND(Table1[Type]="Dispatch",Table1[Status]="Yes"),Table1[Rank]=1),1,0)

    Second=IF(AND(Table1[Type]="Redispatch",Table1[Status]="Yes"),1,0)



    Finally, create a “Resuat_label” column to calculate the pricing agreement for the Claim like the following screenshot. The rows including value is 1  in  “Resuat_label” are equal to the yellow you highlighted.  

    Label=IF(AND(AND(Table1[Type]="Dispatch",Table1[Status]="Yes"),Table1[Rank]=1),1,IF(AND(Table1[Type]="Redispatch",Table1[Status]="Yes"),1,0))
    
    Result_label=IF(CALCULATE(SUM(Table1[Label]),ALLEXCEPT(Table1,Table1[Claim]))=1,Table1[Label],Table1[First])



    Create a measure and pivot table show what you want.

    measure:=CALCULATE(COUNTA(Table1[Claim]),FILTER(Table1,Table1[Result_label]=1))

      



    If you have any other issue, please feel free to ask.

    Best Regards,
    Angelia


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Friday, January 6, 2017 3:39 AM
  • Hi Kieran. I did in the first post. 

    https://www.dropbox.com/s/qu67vikccr1j7r1/Book3.xlsx?dl=0

    Friday, January 6, 2017 10:56 AM
  • Well done Angelia,

    I believe you unpacked the requirements very skillfully indeed!

    Kind Regards,

    Kieran.


    Kieran Patrick Wood http://www.innovativebusinessintelligence.com http://uk.linkedin.com/in/kieranpatrickwood http://kieranwood.wordpress.com/

    Friday, January 6, 2017 12:49 PM
  • Thanks Angelia. A couple questions:

    1) How does the rankx work on just the subsets of individual claims? That is, can you walk me through the logic of the rankx formula? 

    2) My data sets have 5 million rows, won't these calculated columns dramatically slow down my model? 

    3) The order of these things is not set in stone. That is, the redispatch with a "yes" status might not be second, but rather third or fourth. The idea is just to find if it exists in the claim. 

    4) Can you explain the logic of this and what it is doing exactly...specifically the ALLEXCEPT (isn't that similar to Earlier in that it is calculating for each claim?) =if(CALCULATE(sum(Table1[Label]),ALLEXCEPT(Table1,Table1[Claim]))=1,Table1[Label],Table1[First])

    Thanks!!

    Friday, January 6, 2017 5:58 PM
  • Yellow?
    Thursday, January 12, 2017 1:54 AM
  • Hi Albo44,

    1. The syntax of rankx function: RANKX(<table>, <expression>[, <value>[, <order>[, <ties>]]]).If you only want to order the subset, please create the subset using filter, then place the subset the table location.

    2. If you have 5 million row, it will run slowly. I suggest you import the data to database. Get the expected records using SQL statement.

    3. For your requirement, it needs to a dynamic parameter, we are not achieve in PowerPivot, it should be developed. You can post your thread to Excel developer forum for professional support.

    4. Yes, ALLEXCEPT is similar to Earlier function. But there are difference, ALLEXCEPT removes all context filters in the table except filters that have been applied to the specified columns, which can be used either calculated column or measure. EARLIER function returns the current value of the specified column in an outer evaluation pass of the mentioned column, which succeeds if there is a row context prior to the beginning of the table scan.

    Best Regards,
    Angelia


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thursday, January 12, 2017 7:01 AM
  • Thank you kindly Angelia. 
    Friday, January 13, 2017 6:27 PM