Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
74 | |
51 | |
45 | |
16 | |
12 |