Top 10 customer by sale except for Unknown Member

[mmmm]

Hi,

I have just wanted to calculate  Top 10 customer by sale except for Unknown Member

I have just used the below code. But it does not escape the "unknown" member. 

CALCULATE(

[TOTALSALE],

FILTER(

VALUES(Customer[Customer]) ,

RANKX( FILTER(all(Customer[Customer]),Customer[Customer] <> "Unknown") ,[TOTALSALE],,DESC)<=10

)

)

[v-lid-msft]

Hi @mmmm ,

We can try to use the following measure to meet your requirement:

Top10Sales = 
VAR t =
    FILTER (
        SUMMARIZE ( 'Customer', Customer[Customer], "TotalSales", [TOTALSALE] ),
        [Customer] <> "Unknown"
    )
VAR ti =
    FILTER (
        ADDCOLUMNS ( t, "Rank", RANKX ( t, [TotalSales],, DESC ) ),
        [Rank] <= 10
    )
RETURN
    SUMX ( ti, [TotalSales] )

If it doesn't meet your requirement, kindly share your sample data and expected result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlB]

Hi @mmmm 

Can you share the pbix (or a simplified versionthat reproduces the problem)? Including the definition of the [TOTALSALE] measure

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  

[mmmm]

Hi Please find the attached file for reproducing the issue.

Pbix_File 

[v-lid-msft]

Hi @mmmm ,

Sorry for that, but this file seems as same as we have shared, if you can not download it, please open the link in a incognito browser windows.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mmmm]

Sorry For That I upload the Wrong One.

Please find the attached link

New_Pbix 

[mmmm]

OK. I just got the problem . I have to eliminate the unknown Member from both in the filter.

[v-lid-msft]

Hi @mmmm ,

Glad to hear that you have resolved your problem. sorry for that we did not even come up the simple solution before.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.