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.
I'm hoping to get some help building a measure that doesn't double-count - and my fact table double-counts. I've got an Exposure fact table with FK Product. A customer can purchase multiple products from each product line. Exposure[Product] is related to Products[Product] (PK). I want to sum over Exposure[Value], filtering only on the Products[Product] values that generate the largest Exposure[Value] sum in each Products[Product Line]. I'm so stuck.
Here's my thought process:
1. Determine the largest product in each product line. There's only one slicer right now on Products[Product] - as we may want to dynamically exclude some products from consideration. Don't know how to do this. I visualize this as taking the pre-filtered Products table, adding a column for sum(Exposure[Value]), grouping by [Product Line], calculating each group maximum, filtering to only keep each group max line, and returning the values of Products[Product]
2. Create a largest total measure using this. Since I'm new, I'm sure I don't have the syntax right, but this seems easier. Something like Largest Total := calculate(sum(Exposure[Value], filter(Products[Product] IN (variable from 1)))
Please note, I am restricted to using Power Pivot in Excel. Figuring out how the calculated tables work has been challenging 🙂
Any suggestions would be greatly appreciated. Thanks!
Solved! Go to Solution.
If anyone comes across this, I figured it out! The measure defined here for "Sales Top 3 Products" using the topn and keepfilters functions was exactly what I was looking for: Ranking – DAX Patterns.
If anyone comes across this, I figured it out! The measure defined here for "Sales Top 3 Products" using the topn and keepfilters functions was exactly what I was looking for: Ranking – DAX Patterns.
I've been able to make some possible progress on this in Power BI by creating 2 calculated tables, but am still stuck.
Here's what I did.
1. Created a table to summarize totals by Product Line and Product:
Table 3 = summarizecolumns(Data[Product Line], Data[Product], "Total", sumx(Data, Data[Value]))
2. Created a table off of Table 3 to return a list of only the max products
Hi @Jihwan_Kim, thanks - I saw some other posts similar to this, and I couldn't make the sumx work for me. A few complicating factors:
1 - I need to get group sums before taking the max - I have multiple records of Data[Value] for each Data[Product Line], and max(Data[Value]) just takes the largest individual record.
2 - I'm hoping to take this a step further and identify the top products by name. Instead of 57, 80, 96, 72 - I would have a 1 column table showing P01, P04, P10 and P15 - with my data, ties aren't possible and there will be a unique winner.
Hi,
I am not sure how your datamodel looks like, but I tried to create a sample pbix file like below.
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 datamodel.
Expected result measure: =
SUMX ( VALUES ( Data[Product line] ), CALCULATE ( MAX ( Data[Value] ) ) )
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.
User | Count |
---|---|
65 | |
46 | |
20 | |
18 | |
15 |
User | Count |
---|---|
119 | |
41 | |
40 | |
28 | |
22 |