Calculated table for TOP 3 clients

[mihai7]

Hello

Please help me to calculate in DAX this table.

I have the initial table named "Account balance" which contains all client invoices.

I must display in another table only the data for top five clients after column "Value", without considering account "7888918333".

So first must do SUM on column "Value" than order DESC, see which are top 3, show the columns only for these clients and filer <> 7888918333.

The new table must contain all columns.
I tried to do something like this but even if i obtain the total value correct, when i filter by report date or company code , i obtain wrong values.
here i exemplified only for Report date 31.12.2023 but i have this for all months of 2023

Top15TableA =
VAR Top15Clients2 =
TOPN(
3,
VALUES('Account balance'[Client name]),
CALCULATE(SUM('Account balance'[Value])),
DESC
)
RETURN
SELECTCOLUMNS(
FILTER(
'Account balance',
'Account balance'[Client name] IN Top15Clients2 &&
'Account balance'[Account] <> "7888918333"
),
"Value", 'Account balance'[Value],
"Client name", 'Account balance'[Client name],
"Company code", 'Account balance'[Company code],
"Invoice", 'Account balance'[Invoice],
"Account", 'Account balance'[Account],
"Report date", 'Account balance'[Report date]
)

Thank you.

 

[Chihiro]

Are you doing this in Excel Data Model or is it in PowerBI Data model?

If I recall Excel data model doesn't support calculated tables. I'd recommend doing most of the operation in PowerQuery stage.

If using PowerBI, then you can simply do it like below.

Table 2 =
VAR _temp =
    SUMMARIZE (
        FILTER ( 'Table', [Account] <> "77888918333" ),
        'Table'[Client name],
        "value", SUM ( 'Table'[Value] )
    )
VAR clients =
    SELECTCOLUMNS ( TOPN ( 3, _temp, ''[value] ), ''[Client name] )
RETURN
    FILTER ( 'Table', 'Table'[Client name] IN clients )

 

[mihai7]

There is an error somewhere in the logic because although when i don't apply any filter, the TOP15 is calculated correct , but when i filter first company code, it brings 14 positions, for the second, 14 and for the third , 13 positions.
There are clients with the same name in all 3 company codes.This could be a problem?

 

[Chihiro]

In DAX calculated tables are only evaluated upon data load. And not when you make changes to slicer selection.

If you need it to be interactive, you need significantly more complex set up. Or you can just use built-in filters to limit what's shown.

As well, you need to replace ''[Client name] with actual column in your table (i.e. ''[Nume Client]).