Forum Discussion
Calculation with virtual table
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
)
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_KimThank you very much, this is exactly what I need.
2 Replies
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_KimThank you very much, this is exactly what I need.