Measure to sum up the combinations from other rows

[Asingh31]

Hi,

Could someone please help me in creating the measure for this calculation.
Product Column is  a combination of products.
For eg. Product 1 and Product 2 are as a combination in 3 rows ( 1 ,2, 3) that's why sum 18.

Product	Count
Product 1 , Product 2	10
Product 1 , Product 2, Product 3	5
Product 2, Product 3	12
Product 1 , Product 2, Product 3, Product 4	3
Product 1 , Product 3	15

Best,
AS

[danextian]

Hi @Asingh31 

This was a tricky one. I had to spliteach product in the list into its own individual row. Then, I duplicated that table and created up three new measures to work with it.

Please see the attached pbix.

FilteredProduct = 
VAR productcount =
    COUNTROWS ( Split2 )
RETURN
    productcount
        <= CALCULATE (
            COUNTROWS ( Split ),
            KEEPFILTERS (
                Split[Individual Products] IN VALUES ( Split2[Individual Products] )
            )
        )

Count in Split1 = 
SUMX (
    FILTER (
        SUMMARIZECOLUMNS (
            Split[Sales Order],
            Split[Count],
            "@filter", [FilteredProduct]
        ),
        [@filter]
    ),
    [Count]
)

Total in Split2 =
SUMX (
    VALUES ( Split2[Product] ),
    SUMX (
        FILTER (
            SUMMARIZECOLUMNS (
                Split[Sales Order],
                Split[Count],
                "@filter", [FilteredProduct]
            ),
            [@filter]
        ),
        [Count]
    )
)

[Asingh31]

Hi @danextian , wow amazing. I'll go through the solution and if I don't understand something will write you back.

Thank you for the solution.

Cheers 

[danextian · Accepted solution]

FilteredProduct = 
VAR productcount =
    COUNTROWS ( Split2 )
RETURN
    productcount
        <= CALCULATE (
            COUNTROWS ( Split ),
            KEEPFILTERS (
                Split[Individual Products] IN VALUES ( Split2[Individual Products] )
            )
        )

VAR productcount:

Counts the number of rows in the Split2 table within the current filter context., ie based on the slicer selection from Split2

RETURN Statement:

Compares productcount with the result of the CALCULATE function:

• COUNTROWS(Split): Counts the rows in the Split table.
  KEEPFILTERS with IN VALUES: Ensures that the filter from Split2[Individual Products] is applied to
• Split[Individual Products], so only rows with matching products are considered.
  The measure returns TRUE if the row count in Split2 (productcount) is less than or equal to the filtered row count in Split. Otherwise, it returns FALSE.

Total in Split2 =
SUMX (
    VALUES ( Split2[Product] ),
    SUMX (
        FILTER (
            SUMMARIZECOLUMNS (
                Split[Sales Order],
                Split[Count],
                "@filter", [FilteredProduct]
            ),
            [@filter]
        ),
        [Count]
    )
)

Outer SUMX:

Iterates over each unique value in Split2[Product].

Inner SUMX:

Processes a filtered table created by:

• SUMMARIZECOLUMNS:
  • Groups data from the Split table by Sales Order and Count.
    Adds a calculated column @filter, which evaluates the FilteredProduct measure for each group.
• FILTER:
  • Keeps only the rows where @filter is TRUE (i.e., the conditions in FilteredProduct are met).

The result of the inner SUMX is evaluated for each row in VALUES ( Split2[Product] ) in the outer SUMX and then summed up.

[Asingh31]

Hi @danextian , Thank you for the detailed explanation. Appreciate it.

Best,
AS

[bhanu_gautam]

@Asingh31 , Try using

dax
Total Count for Combinations =
SUMX(
'Table',
VAR CurrentRowProducts = 'Table'[Product]
RETURN
SUMX(
FILTER(
'Table',
CONTAINSSTRING(CurrentRowProducts, 'Table'[Product])
),
'Table'[Count]
)
)

[Asingh31]

Hi @bhanu_gautam ,

Thank you for your response but I am not getting desired result.

[danextian]

Hi @Asingh31 

The question is how do we know that the count pertains to a specific product in product column when there are multiple products in there? I'm guessing Product 4 = 3, Product 3 = 5 and Product 2 = 10 for the first 3 rows. But rows 4 and 5 dont follow the aformentioned logic.  Always provide a workable sample data (not an image), the expected result and a concrete reasoning behind.

[Asingh31]

Hi,
These products combinations are single orders. SO each row is an order where some one ordered 2 products or more. Now I have to calculate how many times these combinations are ordered. 
So in my example:
Product 1 and Product 2 (only these 2 ) are ordered 10 times. But in second row and 3rd row Product 1 and Product 2 is also there and being ordered. So I want the sum of these 3 rows for Product 1 and Product 2 combination.
Hope it expalins.
Thank you.

[bhanu_gautam]

@Asingh31 , Can you share sample data

[Asingh31]

done

[amitchandak]

@Asingh31 , You can use Visual calculation of Window function for that

Visual calculation is running sum

Master Visual Calculations in Power BI- February 2024 Update RUNNINGSUM, RANGE, MOVINGAVERAGE, COLLAPSE, COLLAPSEALL, EXPAND, EXPANDALL, FIRST, LAST, PREVIOUS, and NEXT
https://www.youtube.com/watch?v=bKD9T0EWgQo&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L

In Window function you have to use order by product

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc