Hello! I’m a new member, and new to Power BI. Here’s my first complex DAX formula.
My question: Is there a better way? I’d like it to be more “consumable” across other visualizations. I’d like it to honor contexts from SLICERS but ignore all context from visuals. Is there a way?
Description:
As an exercise, I wanted to report customer sales for the top 3 products across the company. I’m using the Enterprise DNA sample database from the “Mastering DAX” tutorials. So the 3 products I want are PRODUCT 1, PRODUCT 2, and PRODUCT 7.
I really struggled finding a way to make it ignore unwanted contexts and came up with CALCULATETABLE. Am I over complicating this?
Suggestions?
I’m thinking a virtual table in a VAR would be more efficient because it would only be rendered once. Am I missing something? It seems like VARs are partly collaborative (with stuff below the RETURN), and partly not.
The weakness is if I modify the visualization by adding other groupings (Lookup table columns) to the visualization like City, or decide to use it for a different visualization not sorted by Customer or Product, or in Measure Branching where it might get used anywhere, it may fall apart due to inherited contexts, and fail to use the desired 3 best sellers (if that’s what the user specification required).
Would “ALLEXCEPT” accomplish this?
I realize not all designs would want to ignore other contexts, but for the sake of this exercise, let’s just assume that’s how my user wants it. For this screen, they’re tracking who’s consuming the top x products their factory produces.
Measure definition:
Products Sales for Top 3 Products =
// Returns totals for the same 3 products across all customers.
VAR ProductContext = values(Products[Product Name])
VAR TopProducts = // Table containing top 3 products across all customers
CALCULATETABLE(
TOPN( 3, ALL( Products), [Total Sales] ),
REMOVEFILTERS( Customer ))
RETURN
CALCULATE( [Total Sales],
TopProducts, // Only add sales for the top products
ProductContext) // Return Products context for Visualizations
Note: Here is a similar measure (closely mirroring one Sam presented on a video I Googled) that doesn’t remove the Customer context, so it would be the top 3 for each customer (or region, or city, etc).
Sales for Top 3 Product by Customer =
var ProductContext = Values( Products[Product Name] )
RETURN
CALCULATE( [Total Sales],
TOPN( 3, ALL( Products), [Total Sales] ), // Only add sales for the top products
ProductContext) // Return product context
Also note: Here’s another variation to grab those exceeding a physical threshold. I could see replacing “9000000” with a slider value…
Products over $9M only =
var ProductContext = Values( Products[Product Name] )
var TopProducts = // Table of products with total sales > $9M for date range across all customers
CALCULATETABLE(
filter(
ALLSELECTED( Products ),
[Total Sales] > 9000000 ), // The test DB has 3 products in 2020 over the arbitraty threshold of $9M.
REMOVEFILTERS( Customer ))
RETURN
CALCULATE( [Total Sales],
TopProducts, // Restrict to top products defined above
ProductContext ) // Return Product context or customer’s total will show on every line.
Side note: I don’t see how to upload the PBI file or code snippets so they retain their formating. Is there a video on this?
