DAX text dynamic segmentation - how to sum similar orders with filter

[xislvh]

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.

[OwenAuger · Accepted solution]

Hi @xislvh

You should use a Basket Analysis pattern here rather than Dynamic Segmentation.

www.daxpatterns.com/basket-analysis/

1. The 'Fact'[Category] and Reference[Category] columns must be related with an inactive relationship.
2. Then this measure will do the trick:
   

   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

[xislvh]

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.

[OwenAuger · Accepted solution]

Hi @xislvh

You should use a Basket Analysis pattern here rather than Dynamic Segmentation.

www.daxpatterns.com/basket-analysis/

1. The 'Fact'[Category] and Reference[Category] columns must be related with an inactive relationship.
2. Then this measure will do the trick:
   

   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

[xislvh · Accepted solution]

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!

[OwenAuger]

Sure...

• Basket Analysis is useful where you want to filter one entity based on the presence of a value in another entity. For example you may want to calculate "Sales for Transactions that contain Bikes". You want to temporarily filter Category = Bikes to define the Transactions to include, but show sales of all Categories within those Transactions, not just Bikes. The pattern on DAX Patterns with an inactive relationship to a secondary lookup table is a convenient way of doing this.
• Dynamic Segmentation is useful where you want to group the calculation of a measure into segments, defined on a particular entity. Typically the segments are numeric ranges. Examples would be segmenting Transactions into ranges of value per transaction, or segmenting customers into range of spend per customer. In this pattern, the Segment table is disconnected from the other tables in the model, since it contains min/max values for each range that can't be related to other tables. Dynamic Segmentation is "dynamic" because the segmentation is determined at query time within the filter context, not pre-computed for each row in a calculated column (as it is in Static Segmentation).

[xislvh]

Hi Owen,

Thank you once again for your concise explanation.

It makes much more sense to me now 🙂

[xislvh]

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.