cancel
Showing results for
Did you mean:

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.

Helper I

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

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!

1 ACCEPTED SOLUTION
Helper I

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

4 REPLIES 4
Helper I

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

Helper I

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.
Helper I

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.

Super User

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.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors