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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rloski-public
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?

1 ACCEPTED SOLUTION

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

5 REPLIES 5
Ashish_Mathur
Super User
Super User

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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"

 

2022-09-26_17-17-36.jpg

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

ProductIDQuantity SoldQuantity On HandQuantity Available
12007055
230013085
Total500200140

 

When reporting on Product and Location this is the results.

 

ProductIDLocationIDQuantity SoldQuantity On HandQuantity Available
112003030
122004025
213006050
223007035
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.

 

 

 

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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