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.
Hi,
I have a data model as shown in t he below diagram.
I want to calculate the Main Product Sales by Customer.
I can enable bi-direction filtering between Product_Package_Sales and Main_Products and use the Main Product Price to get the right results.
I wanted to use a measure to achieve this.
When I use the below measure it is working. In SUMMARIZE funtion the Customer ID and Main Product ID are from respective dimension tables.
_M Main Product Sales by Customer =
SUMX (
SUMMARIZE (
Product_Package_Sales,
Customers[Customer ID],
Main_Product[Main Product ID]
),
CALCULATE ( SUM ( Main_Product[Main Product Price] ) )
)
But when I use the Customer ID and Main Product ID from the Product_Package_Sales fact table in the SUMMARIZE funtion, the results are wrong. (Result shown in the green highlighted box)
_M Product Test2 =
SUMX (
SUMMARIZE (
Product_Package_Sales,
Product_Package_Sales[Customer ID],
Product_Package_Sales[Main Product ID]
),
CALCULATE ( SUM ( Main_Product[Main Product Price] ) )
)
I would appreciate if anyone could explain why the M Product Test2 is not giving the right results.
So just an aside, what happens if you add the following measure to your model and add it to the same table visual?
_M Main Product Sales by Customer (phil) = SUM ( Main_Product[Main Product Price] )
HI @anandav
Is this a calculated measure being put in a Power BI visual (table?)
If so, then your working version is probably using the following two fields in the visual
Customers[Customer ID],
Main_Product[Main Product ID]
If you removed these fields from your table visual and replaced with these two fields
Product_Package_Sales[Customer ID],
Product_Package_Sales[Main Product ID]
and used the [_M Product Test2] measure as well, it might start to work.
As an aside, I don't think you need to use SUMMARIZE in your measure, but let's see if this works first.
Hi @Phil_Seamark,
1] Is this a calculated measure being put in a Power BI visual (table?)
AV - Yes, these are cluclated measures in a Table visual.
2] If so, then your working version is probably using the following two fields in the visual
Customers[Customer ID],
Main_Product[Main Product ID]
AV- No. I am using the Product_Package_Sales[Customer ID] in the table visual.
3] If you removed these fields from your table visual and replaced with these two fields
Product_Package_Sales[Customer ID],
Product_Package_Sales[Main Product ID]
AV- It is still not working. The [_M Product Test2] is still giving the wrong result as shown in the original image.
4] So just an aside, what happens if you add the following measure to your model and add it to the same table visual?
_M Main Product Sales by Customer (phil) = SUM ( Main_Product[Main Product Price] )
AV- It is giving the same results as the [_M Product Test2] with both cases - a) Customer ID from Customers b) CUstomer ID from Product_Package_Sales
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
105 | |
88 | |
75 | |
66 |
User | Count |
---|---|
126 | |
111 | |
100 | |
83 | |
71 |