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.
I'm looking to create a measure that will allow me to filter out only the top 250 items. I'm connected to a live database, so I cannot create columns.
I have an item, customer, and sales table. What I'm looking to do is create a measure that will give me the top 250 items by sales dollars for 2020 in a market channel. I then want that 250 item list to remain static as i view customer sales on those 250 items. Using the Top N filters will filter out the top 250 items for each customer, but i want to compare the same list of items for each customer.
The goal is to see what customers are/are not buying those top items.
Example:
Customer 1
Item | Sales |
Item 1 | 300 |
Item 2 | 200 |
... | ... |
Item 250 | 50 |
Customer 2
Item | Sales |
Item 1 | 1000 |
Item 2 | 200 |
... | ... |
Item 250 | 10 |
Solved! Go to Solution.
Strange, perhaps try it like this.
Top 250 Item Sales =
VAR _TopN = 10
VAR _TopProducts =
CALCULATETABLE (
TOPN (
_TopN,
ALL ( 'Item'[Item Number External] ),
[Net Sales Product LY], DESC
),
'Market Channel'[Market Channel] = "ED",
REMOVEFILTERS ( Customer ),
ALLEXCEPT ( 'Item', 'Item'[Item Number External] )
)
RETURN
CALCULATE (
[Net Sales Product YTD],
FILTER (
VALUES ( 'Item'[Item Number External] ),
'Item'[Item Number External] IN ( _TopProducts )
)
)
+ IF ( SELECTEDVALUE ( 'Item'[Item Number External] ) IN ( _TopProducts ), 0 )
Strange, perhaps try it like this.
Top 250 Item Sales =
VAR _TopN = 10
VAR _TopProducts =
CALCULATETABLE (
TOPN (
_TopN,
ALL ( 'Item'[Item Number External] ),
[Net Sales Product LY], DESC
),
'Market Channel'[Market Channel] = "ED",
REMOVEFILTERS ( Customer ),
ALLEXCEPT ( 'Item', 'Item'[Item Number External] )
)
RETURN
CALCULATE (
[Net Sales Product YTD],
FILTER (
VALUES ( 'Item'[Item Number External] ),
'Item'[Item Number External] IN ( _TopProducts )
)
)
+ IF ( SELECTEDVALUE ( 'Item'[Item Number External] ) IN ( _TopProducts ), 0 )
This did the trick! The measure seems to be working now! I adjusted back to the 250 and it looks good.
Thank you for your help
That is strange, I can't see anything wrong with your sytnax. Try it like this, moving the + IF to the end makes it easier to turn off for testing.
Top 250 Item Sales =
VAR _TopN = 10
VAR _TopProducts =
CALCULATETABLE (
TOPN (
_TopN,
ALL ( 'Item'[Item Number External] ),
[Net Sales Product LY], DESC
),
'Market Channel'[Market Channel] = "ED",
REMOVEFILTERS ( Customer ),
ALLEXCEPT ( 'Item', 'Item'[Item Number External] )
)
RETURN
CALCULATE (
[Net Sales Product YTD],
FILTER (
VALUES ( 'Item'[Item Number External] ),
'Item'[Item Number External] IN ( _TopProducts )
)
)
+ IF ( VALUES ( 'Item'[Item Number External] ) IN ( _TopProducts ), 0 )
Any chance you can share your .pbix file (post it to one drive or drop box and share the link)?
I very much appreciate your help so far. Unfortunately I'm not at liberty to share the file, and it's connected to a large, live azure db.
I moved the +IF to the end but i get the same error. The issue seems to be that it's expecting a single value where VALUES() is in the formula, but a table is supplied. I'm not sure of any alternative way to make it work though.
If you change both steps to look at the same measure does the error go away?
No i get the same error either way. The measure works without the IF portion. I get the error when I add it in, but the functionality of seeing the total number of top products is important to the report.
Right, forcing the 0 on the blanks but keeping the top 250, try this.
Top 250 Products Sales =
VAR _TopN = 250
VAR _TopProducts =
CALCULATETABLE (
TOPN ( _TopN, ALL ( 'Product'[ProductKey] ), [Sales Amount], DESC ),
REMOVEFILTERS ( Customer ),
ALLEXCEPT ( 'Product', 'Product'[ProductKey] )
)
RETURN
IF ( VALUES ( 'Product'[ProductKey] ) IN ( _TopProducts ), 0 ) +
CALCULATE (
[Sales Amount],
FILTER ( VALUES ( 'Product'[ProductKey] ), 'Product'[ProductKey] IN ( _TopProducts ) )
)
It works on my sample to put the 0 on the rows that are in the topn (I am looking at 10 here) even when I filter to a single customer that only bought a portion of the list.
This is exactly what i need. I'm not sure why i'm getting an error when adding in the if statement and you're not.
I get this error using this updated formula. Note that the format is identical, just with the proper names.
Copy your measure and share it here.
Top 250 Item Sales =
VAR _TopN = 10
VAR _TopProducts =
CALCULATETABLE (
TOPN ( _TopN, ALL ('Item'[Item Number External]), [Net Sales Product LY], DESC ),
'Market Channel'[Market Channel]="ED",
REMOVEFILTERS ( Customer ),
ALLEXCEPT ( 'Item', 'Item'[Item Number External] )
)
RETURN
if(VALUES('Item'[Item Number External]) IN (_TopProducts),0)+
CALCULATE (
[Net Sales Product YTD],
FILTER ( VALUES ( 'Item'[Item Number External] ), 'Item'[Item Number External] IN ( _TopProducts ) )
)
Note: i changed the 250 to 10 for now and the Top Products variable uses Last Year sales, while the calculate function in the return looks at YTD
Try something like this.
Top 250 Products Sales =
VAR _TopN = 250
VAR _TopProducts =
CALCULATETABLE (
TOPN ( _TopN, ALL ( 'Product'[ProductKey] ), [Sales Amount], DESC ),
REMOVEFILTERS ( Customer ),
ALLEXCEPT ( 'Product', 'Product'[ProductKey] )
)
RETURN
CALCULATE (
[Sales Amount],
FILTER ( VALUES ( 'Product'[ProductKey] ), 'Product'[ProductKey] IN ( _TopProducts ) )
)
Without knowing the structure of your model it is difficult to give a more precise example. This measure works against the Contoso sample database.
This works much closer. The problem is that it still shows all products, just with blanks for those outside the top 250. I cant just filter out the blanks becasue when i look at customers, I still want to see all the 250 products, even if there are no sales.
@astano05 , Try measure like
Measure =
Var _tab = TOPN(250,all(Table[Item]),[Sales],DESC)
return
calculate([sales], filter(Table, Table[item] in _tab))
Thanks for the response. I get an error with that measure that the visual can't be displayed.
I used this measure which is getting me close:
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 |
---|---|
110 | |
94 | |
80 | |
67 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |