Calculation with virtual table

[flaviobdsti]

Hi guys! I hope they are fine!
I need your help!

I set up a virtual table (as shown in the image below) that shows the customer code and the total sum of sales grouped by customer.

But I have difficulty working with virtual table. I'm trying to do two measurements:

a) total number of customers with sales greater than 30k

b) sum the total sales of the top 5 customers

Can you help me? Thank you very much in advance!

I used this script for creat my virtual table

SUMMARIZE (
    TABLE,
    TABLE[CODE],
    "SALES_TOTAL", SUM ( TABLE[SALES] )
)

This code works in Dax Studio, but in Power BI occorr erro, in Dax Studio it's possible see top 5...

TOPN (
    5,
    ADDCOLUMNS (
        VALUES ( TABLE[CODE] ),
        "TOTAL", CALCULATE ( SUM ( TABLE[SALES_TOTAL] ) )
    ),
    [TOTAL], DESC
)

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure whether I understood your question correctly, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

I hope the below can provide some ideas on how to create a solution for your data model.

Sales measure: = 
SUM( 'Table'[Sales] )

Customer count over 30K: = 
COUNTROWS ( FILTER ( DISTINCT ( 'Table'[Code] ), [Sales measure:] >= 30000 ) )

Top 5 customers sales: =
CALCULATE (
    [Sales measure:],
    KEEPFILTERS ( TOPN ( 5, ALL ( 'Table'[Code] ), [Sales measure:], DESC ) )
)

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure whether I understood your question correctly, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

I hope the below can provide some ideas on how to create a solution for your data model.

Sales measure: = 
SUM( 'Table'[Sales] )

Customer count over 30K: = 
COUNTROWS ( FILTER ( DISTINCT ( 'Table'[Code] ), [Sales measure:] >= 30000 ) )

Top 5 customers sales: =
CALCULATE (
    [Sales measure:],
    KEEPFILTERS ( TOPN ( 5, ALL ( 'Table'[Code] ), [Sales measure:], DESC ) )
)

[flaviobdsti · Accepted solution]

Jihwan_Kim

Thank you very much, this is exactly what I need.