Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I
Helper I

Aggregating when value is tied to one column in table with multicolumn key

I am using SSAS tabular that I am reporting on with Power BI.  I am trying to figure out the modeling of this scenario as well as the DAX that I will need to report on this.


I have an inventory table.  It currently presents the current inventory, so date is not an issue.  There are 3 dimensios to this fact.

  • Product
  • Location
  • Lot

The problem is that the majority of the numeric attributes are tied to the Product.  Many of these attributes are not additive.  At least one isn't.  Another group of numeric attributes are tied to the location.   All of these are additive (at least while the inventory relates to just the current state). One numeric additive attribute is tied to the full key.

If do a standard sum on one of the numeric attributes, the numbers will not reflect reality.  I should count each value once for each product.  The same is true of the product-location values.  I don't want the number increased by the number of lots that are in the location.

I am not sure how much detail I can share.

First question:  is this a good design?  Until we add a date dimension to this, I could move the product attributes to the products table.  The Product-Location attributes need to be in a fact table.

Second question:  Assuming that the Product Location attributes are in the fact table, can someone provide an example of DAX that I could use that would sum the Product-Location attributes without duplicating the values?



You may download my PBI file from here.

Hope this helps.


Ashish Mathur

View solution in original post

Super User
Super User


To get help for the second question, share some data to work with and show the expected result.

Ashish Mathur

My apologies.  Here is a Power Query to create the source table:



    Source = #table({"ProductID","LocationID","LotID","Quantity Sold", "Quantity On Hand", "Quantity Available"},{{1,1,1,200,30,5}
, {1,1,2,200,30,25}
, {1,2,3,200,40,10}
, {1,2,4,200,40,15}
, {2,1,5,300,60,10}
, {2,1,6,300,60,40}
, {2,2,7,300,70,20}
, {2,2,8,300,70,15}
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ProductID", Int64.Type}, {"LocationID", Int64.Type}, {"LotID", Int64.Type}, {"Quantity Sold", Int64.Type}, {"Quantity On Hand", Int64.Type}, {"Quantity Available", Int64.Type}})
    #"Changed Type"



A couple results that I want.  Report at the Product level

ProductIDQuantity SoldQuantity On HandQuantity Available


When reporting on Product and Location this is the results.


ProductIDLocationIDQuantity SoldQuantity On HandQuantity Available
Total 500200140


Thank you for responding let me know if you need any more information.


My friend Kevin Arnold suggested viewing this as a semiadditive pattern.





You may download my PBI file from here.

Hope this helps.


Ashish Mathur

Here is a summary of the DAX @Ashish_Mathur  added to the data model.  You added five measures:

  • QA
  • QoH
  • QoH inter
  • QS
  • QS inter


This is just a measure to reflect the sum of Quantity Available.

QA = SUM(Data[Quantity Available])


QoH is a sum of Quantity on hand, where it sums at the product/location grain.

To do this, @Ashish_Mathur added a measure that I initially couldn't figure out called QoH inter.

QoH inter = MIN(Data[Quantity On Hand])

It made sense when looking at QoH.

QoH = SUMX(generate(VALUES(Data[LocationID]),VALUES(Data[ProductID])),[QoH inter])

Generate creates a  cartesian join of the LocationID and ProductID.  As I read this, this gets a sum of the QoH inter for each combination locationid and productid.  The locationid and productid will have one value, the minimum Quantity on Hand for that combination.


This is the Quantity Sold.  The grain for this is product.

Again there is an intermediate measure QS inter

QS inter = MIN(Data[Quantity Sold])

The Dax for QS is similar to that of QoH.  The difference is that there is no need for a cartestion join.

QS = SUMX(VALUES(Data[ProductID]),[QS inter])

This provides a sum of the minimum Quantity Sold for each product.

Thank you for the help.  If I have misstated how any of these formulas work or am just not clear, please clarify.

You are welcome.

Ashish Mathur

Helpful resources

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.


Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.


Fabric certifications survey

Certification feedback opportunity for the community.