Calculated table based on TOP 5 - return invoices for those in TOP 10

[mihaistanca7]

Hello

Please help me to calculate in DAX this table.

I have the intial table named "Balance" which contains all client invoices.

I must display in another table the invoices for top five clients after column "Balance", without considering account "Accoun3".

So first i must do SUM on column "Balance" than order DESC, see which are top 5, show invoices for these clients and filer <> Acount 3.

The new tabel must contain only  columns "Company code, Client name, Invoice ID, Balance and local account

Thank you.

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file if it suits your requirement.

Expected result table = 
VAR _t =
    ADDCOLUMNS (
        SUMMARIZE ( Balance, Balance[Company code], Balance[Client name] ),
        "@balance total", CALCULATE ( SUM ( Balance[Balance] ) )
    )
VAR _topfive =
    WINDOW (
        1,
        ABS,
        5,
        ABS,
        _t,
        ORDERBY ( [@balance total], DESC ),
        PARTITIONBY ( Balance[Company code] )
    )
VAR _topfivelist =
    SUMMARIZE ( _topfive, Balance[Company code], Balance[Client name] )
VAR _expectedresult =
    CALCULATETABLE (
        FILTER ( Balance, Balance[Local account] <> "Account 3" ),
        TREATAS ( _topfivelist, Balance[Company code], Balance[Client name] )
    )
RETURN
    SUMMARIZE (
        _expectedresult,
        Balance[Company code],
        Balance[Client name],
        Balance[Invoice ID],
        Balance[Balance],
        Balance[Local account]
    )

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file if it suits your requirement.

Expected result table = 
VAR _t =
    ADDCOLUMNS (
        SUMMARIZE ( Balance, Balance[Company code], Balance[Client name] ),
        "@balance total", CALCULATE ( SUM ( Balance[Balance] ) )
    )
VAR _topfive =
    WINDOW (
        1,
        ABS,
        5,
        ABS,
        _t,
        ORDERBY ( [@balance total], DESC ),
        PARTITIONBY ( Balance[Company code] )
    )
VAR _topfivelist =
    SUMMARIZE ( _topfive, Balance[Company code], Balance[Client name] )
VAR _expectedresult =
    CALCULATETABLE (
        FILTER ( Balance, Balance[Local account] <> "Account 3" ),
        TREATAS ( _topfivelist, Balance[Company code], Balance[Client name] )
    )
RETURN
    SUMMARIZE (
        _expectedresult,
        Balance[Company code],
        Balance[Client name],
        Balance[Invoice ID],
        Balance[Balance],
        Balance[Local account]
    )

[mihaistanca7]

Hi and thank you for answer

There is an error at line 15 and i don't understand why

[Jihwan_Kim]

Hi, please share your sample pbix file.

Thanks.

[mihaistanca7]

Hi

Because i am a new user i cannot attach any file, pbix or XLSX.Can i use email or other method?

Thank you

[Jihwan_Kim · Accepted solution]

HI,

Please try using Onedrive, Dropbox, or googledrive, and then share the link of the file.

Thanks.