Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
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?
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi,
To get help for the second question, share some data to work with and show the expected result.
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.
Hi,
You may download my PBI file from here.
Hope this helps.
Here is a summary of the DAX @Ashish_Mathur added to the data model. You added five measures:
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
91 | |
86 | |
76 | |
66 |
User | Count |
---|---|
149 | |
117 | |
111 | |
106 | |
95 |