Semi-additive summing in Power Pivot - filtering on largest product in each line

[karen578]

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!

[karen578 · Accepted 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. 

[karen578]

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

Max Lines = SELECTCOLUMNS(SUMMARIZECOLUMNS('Table 3'[Product Line], "Product", calculate(values(Data[Product]), filter('Table 3',[Total] = max('Table 3'[Total])))), "Product", [Product])

3. Created a filtered total measure using the list in Max Lines as a filter:

filtered total =

 calculate([value], filter(Data, Data[Product] IN values('Max Lines'[Product])))

Not sure if this is a decent approach and I'm still having 2 issues:

1.  My first calculated table is not respecting the filter I have on Data[Product] - if the user isn't selecting this in the slicer, I'd like it to show 0/blank so that another Product can be chosen as the max

2. Since I need to ultimately do this in Power Pivot, I can't be calculating additional tables. I tried to put these intermediate values (tables) into variables, but I'm not sure how to reference columns in variables.

[karen578]

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.

[Jihwan_Kim]

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] ) ) )