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 All,
I would be most grateful for some help with my DAX formula for a calculated measure please.
I have a fact table 'Fact' and a parameter table 'Reference' (disconnected slicer).
'Fact' consists of three columns: Transactions, Category, Sales - this is at transaction level, i.e. if a
customer purchase multiple category products in the same transaction, there would be multiple rows of data
reflecting the same unique Transaction ID and respective category and sales (see below):
'Reference' consists of the column Category (distinct values of Category from 'Fact'), used to filter the
transactions in 'Fact' to compute sales dynamically based on user filter selection.
Help required:
How do I create a calculated measure to sum the sales of all transactions with the same Transaction ID that
consist of at least 1 category selected by the 'Reference' parameter table (there can only be 1 distinct
selection.
For the above example, if I were to select "Bikes" in 'Reference' slicer, the calculated measure should reflect
36 (which is the sum of all rows of data for Transaction ID 11110 and 11112, i.e. Bikes with other categories
bought). Currently I am getting the output 10 which is the sum of the 'Bikes' data row only.
This is my formula:
SalesbyProductSingle:=
IF (
ISFILTERED ( Reference[Category] ),
IF (
HASONEVALUE ( Reference[Category] ),
CALCULATE (
SUM ( Fact[Sales] ) ,
FILTER (
VALUES ( Fact ),
COUNTROWS (
FILTER (
Reference,
Fact[Category] = Reference[Category]
&& Fact[Transaction] = EARLIER ( Fact[Transaction] )
)
) > 0
)
),
BLANK ()
),
SUM ( Fact[Sales] )
)
Thank you.
Solved! Go to Solution.
Hi @xislvh
You should use a Basket Analysis pattern here rather than Dynamic Segmentation.
www.daxpatterns.com/basket-analysis/
SalesByProduct = CALCULATE ( SUM ( 'Fact'[Sales] ), CALCULATETABLE ( SUMMARIZE ( 'Fact', 'Fact'[Transaction] ), ALL ( 'Fact'[Category] ), USERELATIONSHIP ( 'Fact'[Category], Reference[Category] ) ) )This will calculate sales, with Fact'[Transaction] filtered to those transactions containing the selected values of Reference[Category], intersected with the current filter context.
Regards,
Owen
Hi Owen,
Big thanks for your solution.
You are right, I was referring to the Dynamic Segmentation in daxpatterns website instead of Basket Analysis pattern.
Just curious, would you mind sharing why the Dynamic Segmentation pattern would not work in this instance but Basket Analysis pattern would? This is to aid my further understanding of DAX and usage.
Thank you once again!
I broke down my formula into smaller chunks to troubleshoot.
The issue seems to be the iteration for "&& Fact[Transaction] = EARLIER ( Fact[Transaction] )" not working properly.
I have yet to figure out how to nest the formula correctly such that the Sales for respective Transaction ID are summed correctly based on a Category selection in 'Reference' parameter table (disconnected slicer).
Any help would be most appreciated.
Hi @xislvh
You should use a Basket Analysis pattern here rather than Dynamic Segmentation.
www.daxpatterns.com/basket-analysis/
SalesByProduct = CALCULATE ( SUM ( 'Fact'[Sales] ), CALCULATETABLE ( SUMMARIZE ( 'Fact', 'Fact'[Transaction] ), ALL ( 'Fact'[Category] ), USERELATIONSHIP ( 'Fact'[Category], Reference[Category] ) ) )This will calculate sales, with Fact'[Transaction] filtered to those transactions containing the selected values of Reference[Category], intersected with the current filter context.
Regards,
Owen
Hi Owen,
Big thanks for your solution.
You are right, I was referring to the Dynamic Segmentation in daxpatterns website instead of Basket Analysis pattern.
Just curious, would you mind sharing why the Dynamic Segmentation pattern would not work in this instance but Basket Analysis pattern would? This is to aid my further understanding of DAX and usage.
Thank you once again!
Sure...
Hi Owen,
Thank you once again for your concise explanation.
It makes much more sense to me now 🙂
Hi All,
I would be most grateful for some help with my DAX formula for a calculated measure please.
I have a fact table 'Fact' and a parameter table 'Reference' (disconnected slicer).
'Fact' consists of three columns: Transactions, Category, Sales - this is at transaction level, i.e. if a
customer purchase multiple category products in the same transaction, there would be multiple rows of data
reflecting the same unique Transaction ID and respective category and sales (see below):
'Reference' consists of the column Category (distinct values of Category from 'Fact'), used to filter the
transactions in 'Fact' to compute sales dynamically based on user filter selection.
Help required:
How do I create a calculated measure to sum the sales of all transactions with the same Transaction ID that
consist of at least 1 category selected by the 'Reference' parameter table (there can only be 1 distinct
selection.
For the above example, if I were to select "Bikes" in 'Reference' slicer, the calculated measure should reflect
36 (which is the sum of all rows of data for Transaction ID 11110 and 11112, i.e. Bikes with other categories
bought). Currently I am getting the output 10 which is the sum of the 'Bikes' data row only.
This is my formula:
SalesbyProductSingle:=
IF (
ISFILTERED ( Reference[Category] ),
IF (
HASONEVALUE ( Reference[Category] ),
CALCULATE (
SUM ( Fact[Sales] ) ,
FILTER (
VALUES ( Fact ),
COUNTROWS (
FILTER (
Reference,
Fact[Category] = Reference[Category]
&& Fact[Transaction] = EARLIER ( Fact[Transaction] )
)
) > 0
)
),
BLANK ()
),
SUM ( Fact[Sales] )
)
Thank you.
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 |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |