Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi Experts,
I have a table like below:
Dealer | Target | Actual | ||
Dealer 013 | 82 | 52 | ||
Dealer 012 | 72 | 32 | ||
Dealer 018 | 13 | |||
Dealer 010 | 54 | 10 | ||
Dealer 014 | 32 | 5 | ||
Dealer 016 | 79 | -10 | ||
Dealer 01 | 37 | |||
Dealer 02 | 25 | |||
Dealer 03 | 23 | |||
Dealer 04 | 28 | |||
Dealer 05 | 99 | |||
Dealer 06 | 66 | |||
Dealer 07 | 23 | |||
Dealer 08 | 18 | |||
Dealer 09 | 15 | |||
Dealer 011 | 48 | |||
Dealer 015 | 49 | |||
Dealer 017 | 40 |
I am trying to show the total actual sales by top 10 dealers.
I am using the following dax:
Solved! Go to Solution.
Try
Total sales ( top 10 dealers ) =
VAR DealersWithSales =
FILTER (
ADDCOLUMNS (
ALL ( 'Table'[Dealer] ),
"@sales", CALCULATE ( SUM ( 'Table'[Actual] ) )
),
NOT ISBLANK ( [@sales] )
)
VAR Top10Dealers =
TOPN ( 10, DealersWithSales, [@sales] )
VAR Result =
SUMX ( Top10Dealers, [@sales] )
RETURN
Result
Try
Total sales ( top 10 dealers ) =
VAR DealersWithSales =
FILTER (
ADDCOLUMNS (
ALL ( 'Table'[Dealer] ),
"@sales", CALCULATE ( SUM ( 'Table'[Actual] ) )
),
NOT ISBLANK ( [@sales] )
)
VAR Top10Dealers =
TOPN ( 10, DealersWithSales, [@sales] )
VAR Result =
SUMX ( Top10Dealers, [@sales] )
RETURN
Result
@GreenP Try:
Total Sales(Top 10 Dealers) =
VAR __Table =
TOPN(
10,
FILTER(ALL('Table'),[Actual] <> BLANK()),
CALCULATE(SUM('Table'[Actual]))
)
VAR __Result = SUMX(__Table,[Actual])
RETURN
__Result
PBIX attached below signature.
User | Count |
---|---|
65 | |
46 | |
20 | |
18 | |
15 |
User | Count |
---|---|
120 | |
41 | |
40 | |
28 | |
23 |