Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all,
I am doing a top 5 plus others for my report, and it works fine until i put the month in the columns it will show top 5 for each month and Total column will only add the Top 5 based on the total result.
The measure I created is as below, and this is how it looks before I put the Month in the Columns:
This is how it looks after I put the Month in the columns:
Does anyone has any idea how to resolve this... it seems so hard, and the result I want is just
Mesure:
Top N Sum Sales =
VAR TopSalesTable =
TOPN (
5,
ALLSELECTED ('Unique Customers'),
[Total Sales]
)
VAR TopSales =
CALCULATE (
[Total Sales],
KEEPFILTERS ( TopSalesTable )
)
VAR OtherSales =
CALCULATE (
[Total Sales],
ALLSELECTED ('Unique Customers' )
)
- CALCULATE (
[Total Sales],
TopSalesTable
)
VAR CurrentProd = SELECTEDVALUE ( 'Unique Customers'[Customer name] )
RETURN
IF (
CurrentProd <> "Others",
TopSales,
OtherSales
)
Solved! Go to Solution.
Hi,
I tried to simplify the sample and created a new sample pbix file like below. (top3 and others)
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.
Top 3 sales and others: =
VAR _topthreecustomerlistwholeyear =
TOPN (
3,
ALLSELECTED ( Customer[Customer] ),
CALCULATE ( [Sales total:], REMOVEFILTERS ( 'Calendar' ) ), DESC
)
VAR _topthreefilter =
TOPN ( 3, ALLSELECTED ( Customer[Customer] ), [Sales total:], DESC )
VAR _allsales =
CALCULATE ( [Sales total:], REMOVEFILTERS ( Customer ) )
VAR _othersales =
_allsales - CALCULATE ( [Sales total:], _topthreefilter )
RETURN
SWITCH (
TRUE (),
HASONEVALUE ( Customer[Customer] ),
IF (
SELECTEDVALUE ( Customer[Customer] ) = "Others",
_othersales,
CALCULATE (
[Sales total:],
KEEPFILTERS ( Customer[Customer] IN _topthreecustomerlistwholeyear )
)
),
[Sales total:]
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi,
I tried to simplify the sample and created a new sample pbix file like below. (top3 and others)
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.
Top 3 sales and others: =
VAR _topthreecustomerlistwholeyear =
TOPN (
3,
ALLSELECTED ( Customer[Customer] ),
CALCULATE ( [Sales total:], REMOVEFILTERS ( 'Calendar' ) ), DESC
)
VAR _topthreefilter =
TOPN ( 3, ALLSELECTED ( Customer[Customer] ), [Sales total:], DESC )
VAR _allsales =
CALCULATE ( [Sales total:], REMOVEFILTERS ( Customer ) )
VAR _othersales =
_allsales - CALCULATE ( [Sales total:], _topthreefilter )
RETURN
SWITCH (
TRUE (),
HASONEVALUE ( Customer[Customer] ),
IF (
SELECTEDVALUE ( Customer[Customer] ) = "Others",
_othersales,
CALCULATE (
[Sales total:],
KEEPFILTERS ( Customer[Customer] IN _topthreecustomerlistwholeyear )
)
),
[Sales total:]
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Appreciate your help!! Genius! It works now 🙂
User | Count |
---|---|
57 | |
22 | |
21 | |
19 | |
16 |
User | Count |
---|---|
87 | |
87 | |
52 | |
37 | |
23 |