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

[rloski-public]

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?

[Ashish_Mathur]

Hi,

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

[rloski-public]

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

let
    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}})
in
    #"Changed Type"

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

ProductID	Quantity Sold	Quantity On Hand	Quantity Available
1	200	70	55
2	300	130	85
Total	500	200	140

When reporting on Product and Location this is the results.

ProductID	LocationID	Quantity Sold	Quantity On Hand	Quantity Available
1	1	200	30	30
1	2	200	40	25
2	1	300	60	50
2	2	300	70	35
Total		500	200	140

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

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

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[rloski-public]

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

QA

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

QA = SUM(Data[Quantity Available])

QoH

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.

QS

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.

[Ashish_Mathur]

You are welcome.