Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
karen578
Helper I
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
karen578
Helper I
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

View solution in original post

4 REPLIES 4
karen578
Helper I
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

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

Jihwan_Kim
Super User
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.

 

Jihwan_Kim_0-1681183578314.png

 

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.


Go to My LinkedIn Page


Helpful resources

Announcements
Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors