Slice from total price sum based on all order from...

Thales_Taranto · 11-09-2021 02:34 PM

I’m trying to create a slice that returns our top 3 client based on the total of expenses from him.

First, i created a VC with this formula:

SUM(SELECT(Pedido[Valor Total],
[_THISROW].[Cliente ID]=[Cliente ID]
))
Where Valor Total = Total price of the order(Pedido).
But now i can’t create a slice that returns the top 3 itens based on this sum.

So basically I want a slice that filter the top 3 Clients based on total expenses on ALL orders related to him.

Marc_Dillon

Here is a Slice row filter condition expression for this. Maybe not the best performing solution, but I think it should work (if I understood the requirement correctly):

IN(
  [key-column] ,
  TOP(
    ORDERBY(
      Table[key-column] ,
      [total-VC]
    ) ,
    3
  )
)

Thales_Taranto

After some tests i figure out the result was not correct. I couldn’t get the unique clients expenses ranking. On the image bellow there is an exemple of a incorrect result.

Suposing that “Thales” is one of the 3 clients that have expent more, it’s correct show only ONE (aleatory record) of him but the other two ones must be from a different client. I tried to create a VC to get the unique CLIENT ID’s and then use a select formula but it won’t work due to ref-list comparision error.

What i tried and know it wont work: (Client ID column is ref to Client table)

IN(
[KeyColumn] ,
TOP(
ORDERBY(
SELECT(Table[KeyColumn],[_THISROW].[Client ID]=[UNIQUECLIENT_VC]),
[Total_VC]
) ,
3
)

)

Slice from total price sum based on all order from different clients