Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Power BI DAX

=aggr(if(rank((Sum({<PARTS_TYPE={'Wholegoods'},SHIP_FROM_ORGANIZATION_NAME={'IM','ID'},DATE_TYPE={'ORDERED_DATE'},CYTD={'1'},FiscalMonth=,FiscalQuarter=,FiscalYear=>}OPEN_VALUE_GBP))
+(Sum({<PARTS_TYPE={'Wholegoods'},SHIP_FROM_ORGANIZATION_NAME={'IM','ID'},DATE_TYPE={'ORDERED_DATE'},CYTD={'1'},FiscalMonth=,FiscalQuarter=,FiscalYear=>}GOODS_VALUE_GBP)))<=10,
CUSTOMER_NAME, 'Other'), CUSTOMER_NAME)

 

 

I want to replicate this calculation, but I don't think I can show the result of this QlikView calculation as it contains sales company figures so it is confidential 

Need Help
Show and Tell
Tips and Tricks
  • v-eachen-msft's avatar
    v-eachen-msft
    6 years ago

    Hi Anonymous ,

     

    You could try the following DAX:

    _DAX =
VAR a =
    CALCULATE (
        SUM ( 'Table'[OPEN_VALUE_GBP] ),
        FILTER (
            'Table',
            'Table'[PARTS_TYPE] = 'Wholegoods'
                && 'Table'[SHIP_FROM_ORGANIZATION_NAME] IN { 'IM', 'ID' }
                && 'Table'[DATE_TYPE] = 'ORDERED_DATE'
                && 'Table'[CYTD] = '1'
        )
    )
VAR b =
    CALCULATE (
        SUM ( 'Table'[GOODS_VALUE_GBP] ),
        FILTER (
            'Table',
            'Table'[PARTS_TYPE] = 'Wholegoods'
                && 'Table'[SHIP_FROM_ORGANIZATION_NAME] IN { 'IM', 'ID' }
                && 'Table'[DATE_TYPE] = 'ORDERED_DATE'
                && 'Table'[CYTD] = '1'
        )
    )
RETURN
    TOPN ( 10, 'Table', a + b, ASC )

    Then you could use IF() statement to get the correct "CUSTOMER_NAME".

     

1 Reply

  • v-eachen-msft's avatar
    v-eachen-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    You could try the following DAX:

    _DAX =
VAR a =
    CALCULATE (
        SUM ( 'Table'[OPEN_VALUE_GBP] ),
        FILTER (
            'Table',
            'Table'[PARTS_TYPE] = 'Wholegoods'
                && 'Table'[SHIP_FROM_ORGANIZATION_NAME] IN { 'IM', 'ID' }
                && 'Table'[DATE_TYPE] = 'ORDERED_DATE'
                && 'Table'[CYTD] = '1'
        )
    )
VAR b =
    CALCULATE (
        SUM ( 'Table'[GOODS_VALUE_GBP] ),
        FILTER (
            'Table',
            'Table'[PARTS_TYPE] = 'Wholegoods'
                && 'Table'[SHIP_FROM_ORGANIZATION_NAME] IN { 'IM', 'ID' }
                && 'Table'[DATE_TYPE] = 'ORDERED_DATE'
                && 'Table'[CYTD] = '1'
        )
    )
RETURN
    TOPN ( 10, 'Table', a + b, ASC )

    Then you could use IF() statement to get the correct "CUSTOMER_NAME".