Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Dax for maxif

Hi,

 

Please can you tell me the dax formula to find the column "Maximum Sales In Area"? (for each row it should find the maximum number of sales for the branch with the most sales in the same area)

 

Branch | Area | Sales | Maximum Sales In Area

1 | North | 20 | 40

2 | North | 40 | 40

3 | North | 35 | 40

4 | South | 5 | 12

5 | South | 12 | 12

6 | South | 8 | 12

 

Thanks,

 

CM

 

p.s. I've tried the CALCLATE function with MAX and FILTER but it doesn't allow me to specifiy a different value condition for each row.

 

Need Help
  • Vvelarde's avatar
    Vvelarde
    8 years ago

    Anonymous

     

     

    Hi, lets try with:

    Calculated Column

     

    MaximumSalesinArea =
CALCULATE (
    MAX ( Table2[Sales] );
    FILTER ( Table2; Table2[Area] = EARLIER ( Table2[Area] ) )
)

     

    Regards

     

    Victor

    Lima - Peru

4 Replies

  • Vvelarde's avatar
    Vvelarde
    Icon for Community Champion rankCommunity Champion
    8 years ago

    Anonymous

     

     

    Hi, lets try with:

    Calculated Column

     

    MaximumSalesinArea =
CALCULATE (
    MAX ( Table2[Sales] );
    FILTER ( Table2; Table2[Area] = EARLIER ( Table2[Area] ) )
)

     

    Regards

     

    Victor

    Lima - Peru

  • johnajoseph's avatar
    johnajoseph
    Regular Visitor
    3 years ago

    Hmm... for me, those semicolons needed to be commas. But otherwise worked.

  • tecumseh's avatar
    tecumseh
    Icon for Resolver III rankResolver III
    2 years ago

    Vvelarde 

    I built this faithfully to understand Earlier.
    I am receiving an error message:

    EARLIER/EARLIEST refers to an earlier row context which doesn't exist.

    My DAX:

    MaximumSalesInArea =

    CALCULATE(

        MAX( Table2[Sales] ),

        FILTER(

            Table2,

            Table2[Area] = EARLIER( Table2[Area] )

        )

    )