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_T...
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".

     

v-eachen-msft's avatar
v-eachen-msft
Community 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".