Mix% (Dynamic date filters, NaturalInnerjoin, Summarize)

[barkha]

Input Sample Data:

Customer	SKU	Date	Rank	Quantity
A	S100	Jan-24	1	200
A	S100	Oct-24	2	400
A	S200	Nov-24	3	45
A	S100	Mar-25	4	100
A	S100	Oct-25	5	200
A	S200	Oct-25	5	67
B	S100	Jan-24	1	214
B	S100	Oct-24	2	412
B	S100	Mar-25	4	112
B	S100	Oct-25	5	211
B	S200	Oct-25	5	78
B	S300	Oct-25	5	55

Output Sample Data:

Customer	SKU	CY 2025	CY Mix %
A	S100	300	82%
A	S200	67	18%
B	S100	323	100%

Explanantion of Output Table:
1. Quantity is aggregated for Customer and SKU for current year and previous year (CY) vs (PY) using input table. 
2. For every customer, only those SKUs are retained which are sold in both years (Naturalinnerjoin). Therefore, SKU S200 & S300 are not considered in output table as they are not present in both CY and PY.
3. Calculate Mix% for current year, within in each customer by dividing SKU level quantity by Customer level total quantity.
4. Rank column is according to date. The rank column from input table is used to filter time period dynamically. 

5. I am not able to achieve total at customer level after Naturalinnerjoin in DAX.

DAX: 

MnA =
VAR base_rank = SELECTEDVALUE(BaseCalendar[Rank])
VAR actual_rank = SELECTEDVALUE(ActualCalendar[Rank])
VAR timePeriod = SELECTEDVALUE(TimePeriod[SelectedPeriod])-1

// 1.
VAR tbl1 =  SUMMARIZE(  FILTER( CleanSales,
            CleanSales[rank] >= base_rank - timePeriod && 
CleanSales[rank] <= base_rank ),
        All_CustomerIDs[CustomerNo], All_SKUCode[SKUCode],
        "QuantityB", SUM(CleanSales[Quantity]))
// 2.
VAR tbl2 =  SUMMARIZE(   FILTER( CleanSales,
            CleanSales[rank] >= actual_rank - timePeriod && 
CleanSales[rank] <= actual_rank ),
        All_CustomerIDs[CustomerNo], All_SKUCode[SKUCode],
        "QuantityA", SUM(CleanSales[Quantity]))
// 3.
VAR FullJoinTable2 = NATURALINNERJOIN(tbl1,tbl2)
// 4.
VAR CustomerTotals = ADDCOLUMNS(
        SUMMARIZE(FullJoinTable2, All_CustomerIDs[CustomerNo]),
        "TotalBaseQuantity", CALCULATE(SUMX(FullJoinTable2, [QuantityB])),
        "TotalActualQuantity", CALCULATE(SUMX(FullJoinTable2, [QuantityA])))

// 5. Merge totals back
VAR FullWithTotals =  NATURALLEFTOUTERJOIN(FullJoinTable2, CustomerTotals)
// 6.
VAR MAMB = ADDCOLUMNS(
        FullWithTotals,
        "Mn_A", DIVIDE([QuantityA], [TotalActualQuantity], 0),
        "Mn_B", DIVIDE([QuantityB], [TotalBaseQuantity], 0))
// 7.
// Final calculation: Return the sum of Mn_B across all products in tbl1
RETURN  AVERAGEX(MAMB, [Mn_A])

 
Brief explanation of DAX -
1. Data is filtered first time using Current time period: Dynamic filtering using current start date, previous start date and last-n-months slicers. I have used ranks to filter time periods below. Actual & Base calendars are not connected to sales table.
2. Same data filtered using Base time period.
3. Both of the datasets are inner joined at Customer x SKU level to find common combinations (Naturalinnerjoin). All_CustomerIDs, All_SKUCode are connected to sales table.
4. This table with common combinations is again summarized at customer level to find customer level total.
5. Table from point 4 is joined back on table in point 3 (inner-joined).

6. Calculate mix% by sku level qty/ customer level qty.
7. Return mix% using averagex for each Customer and SKU

Issues in DAX:
1. The calculation fails at point 4. i.e. not able to calculate customer level totals. Please suggest correct DAX.
2. Is return method correct?
3. Can this be optimized? any better method to achieve this type of calculation?

[v-echaithra · Accepted solution]

Hi @Praful_Potphode ,

Please refer to the pbix below attatctched, and refer to the power query steps to get your desired output.

Hope this helps.
Thank you.

[Rufyda]

You can calculate customer-level totals after the NATURALINNERJOIN using ADDCOLUMNS with SUMX(FILTER()) per customer, then merge back with the joined table. This ensures SKU-level Mix% divides correctly by the customer total. Using SUMMARIZECOLUMNS instead of SUMMARIZE can also simplify and optimize the calculation.

Try these solutions and let me know if you need further clarification

Regards,
Rufyda Rahma | MIE

[tamerj1 · Accepted solution]

@barkha 
Ok, I had the chance to implement the solution in the attached sample file.

QAQB = () => 
VAR base_rank =
    SELECTEDVALUE ( BaseCalendar[Rank] )
VAR actual_rank =
    SELECTEDVALUE ( ActualCalendar[Rank] )
VAR timePeriod =
    SELECTEDVALUE ( TimePeriod[SelectedPeriod] ) - 1 
// 1.
VAR tbl1 =
    SUMMARIZE (
        FILTER (
            CleanSales,
            CleanSales[rank] >= base_rank - timePeriod && CleanSales[rank] <= base_rank
        ),
        All_CustomerIDs[CustomerNo],
        All_SKUCode[SKUCode],
        "QuantityB", SUM ( CleanSales[Quantity] )
    ) 
// 2.
VAR tbl2 =
    SUMMARIZE (
        FILTER (
            CleanSales,
            CleanSales[rank] >= actual_rank - timePeriod && CleanSales[rank] <= actual_rank
        ),
        All_CustomerIDs[CustomerNo],
        All_SKUCode[SKUCode],
        "QuantityA", SUM ( CleanSales[Quantity] )
    ) 
// 3.
VAR InnerJoinTable1 =
    NATURALINNERJOIN ( tbl1, tbl2 ) 
RETURN
    InnerJoinTable1

MAMB = () => 
VAR base_rank =
    SELECTEDVALUE ( BaseCalendar[Rank] )
VAR actual_rank =
    SELECTEDVALUE ( ActualCalendar[Rank] )
VAR timePeriod =
    SELECTEDVALUE ( TimePeriod[SelectedPeriod] ) - 1 
// 4.
VAR tbl3 =
    CALCULATETABLE (
        SUMMARIZE (
            FILTER (
                CleanSales,
                CleanSales[rank] >= base_rank - timePeriod && CleanSales[rank] <= base_rank
            ),
            All_CustomerIDs[CustomerNo],
            All_SKUCode[SKUCode],
            "QuantityB", SUM ( CleanSales[Quantity] )
        ),
        ALL ( All_SKUCode[SKUCode] )
    ) 
// 5.
VAR tbl4 =
    CALCULATETABLE (
        SUMMARIZE (
            FILTER (
                CleanSales,
                CleanSales[rank] >= actual_rank - timePeriod && CleanSales[rank] <= actual_rank
            ),
            All_CustomerIDs[CustomerNo],
            All_SKUCode[SKUCode],
            "QuantityA", SUM ( CleanSales[Quantity] )
        ),
        ALL ( All_SKUCode[SKUCode] )
    ) 
// 6.
VAR InnerJoinTable2 =
    NATURALINNERJOIN ( tbl3, tbl4 ) 
// 7.
VAR CustomerTotals =
    GROUPBY (
        InnerJoinTable2,
        All_CustomerIDs[CustomerNo],
        "TotalBaseQuantity", SUMX ( CURRENTGROUP (), [QuantityB] ),
        "TotalActualQuantity", SUMX ( CURRENTGROUP (), [QuantityA] )
    ) 
// 8. Merge totals back
VAR FullWithTotals =
    NATURALLEFTOUTERJOIN ( QAQB(), CustomerTotals ) 
// 9.
VAR MAMB =
    ADDCOLUMNS (
        FullWithTotals,
        "Mn_A", DIVIDE ( [QuantityA], [TotalActualQuantity], 0 ),
        "Mn_B", DIVIDE ( [QuantityB], [TotalBaseQuantity], 0 )
    )
RETURN
    MAMB

CY Quantity = SUMX ( QAQB(), [QuantityB])

CY Mix = AVERAGEX ( MAMB(), [Mn_B] )

PY Quantity = SUMX ( QAQB(), [QuantityA] )

PY Mix = AVERAGEX ( MAMB(), [Mn_A] )

[tamerj1]

@barkha 

I'm extremely sorry! Looks like I just marked my solution as acceptable solution by mistake and I don't know how to undo that! Maybe @v-echaithra can help! 

[v-echaithra]

Hi @tamerj1 ,

No worries, you can easily undo it.

Please click on the three dots (⋯) on the reply that was marked as the solution. You should see an option called “Not the solution”. Selecting that will unmark the reply as the accepted solution.

Hope that helps!

[tamerj1]

Hi @barkha 

I suggest to utilize UDF's to easily store the tables in this measure as I guess same tables will be used in multiple measures. 
Your approach is %100 correct but I believe some steps require little more attention. 
I didn't have the chance to implement and test the following solution but I hope you can do from your end. 

MnA =
VAR base_rank =
    SELECTEDVALUE ( BaseCalendar[Rank] )
VAR actual_rank =
    SELECTEDVALUE ( ActualCalendar[Rank] )
VAR timePeriod =
    SELECTEDVALUE ( TimePeriod[SelectedPeriod] ) - 1 
// 1.
VAR tbl1 =
    SUMMARIZE (
        FILTER (
            CleanSales,
            CleanSales[rank] >= base_rank - timePeriod && CleanSales[rank] <= base_rank
        ),
        All_CustomerIDs[CustomerNo],
        All_SKUCode[SKUCode],
        "QuantityB", SUM ( CleanSales[Quantity] )
    ) 
// 2.
VAR tbl2 =
    SUMMARIZE (
        FILTER (
            CleanSales,
            CleanSales[rank] >= actual_rank - timePeriod && CleanSales[rank] <= actual_rank
        ),
        All_CustomerIDs[CustomerNo],
        All_SKUCode[SKUCode],
        "QuantityA", SUM ( CleanSales[Quantity] )
    ) 
// 3.
VAR InnerJoinTable1 =
    NATURALINNERJOIN ( tbl1, tbl2 ) 
// 4.
VAR tbl3 =
    CALCULATETABLE (
        SUMMARIZE (
            FILTER (
                CleanSales,
                CleanSales[rank] >= base_rank - timePeriod && CleanSales[rank] <= base_rank
            ),
            All_CustomerIDs[CustomerNo],
            All_SKUCode[SKUCode],
            "QuantityB", SUM ( CleanSales[Quantity] )
        ),
        ALL ( All_SKUCode[SKUCode] )
    ) 
// 5.
VAR tbl4 =
    CALCULATETABLE (
        SUMMARIZE (
            FILTER (
                CleanSales,
                CleanSales[rank] >= actual_rank - timePeriod && CleanSales[rank] <= actual_rank
            ),
            All_CustomerIDs[CustomerNo],
            All_SKUCode[SKUCode],
            "QuantityA", SUM ( CleanSales[Quantity] )
        ),
        ALL ( All_SKUCode[SKUCode] )
    ) 
// 6.
VAR InnerJoinTable2 =
    NATURALINNERJOIN ( tbl3, tbl4 ) 
// 7.
VAR CustomerTotals =
    GROUPBY (
        InnerJoinTable2,
        All_CustomerIDs[CustomerNo],
        "TotalBaseQuantity", SUMX ( CURRENTGROUP (), [QuantityB] ),
        "TotalActualQuantity", SUMX ( CURRENTGROUP (), [QuantityA] )
    ) 
// 8. Merge totals back
VAR FullWithTotals =
    NATURALLEFTOUTERJOIN ( InnerJoinTable1, CustomerTotals ) // 
9.
VAR MAMB =
    ADDCOLUMNS (
        FullWithTotals,
        "Mn_A", DIVIDE ( [QuantityA], [TotalActualQuantity], 0 ),
        "Mn_B", DIVIDE ( [QuantityB], [TotalBaseQuantity], 0 )
    ) 
// 10.
// Final calculation: Return the sum of Mn_B across all products in tbl1
RETURN
    AVERAGEX ( MAMB, [Mn_A] )

[v-echaithra]

Hi @barkha ,

May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.

Thank you.

[v-echaithra]

Hi @barkha ,

We’d like to follow up regarding the recent concern. Kindly confirm whether the issue has been resolved, or if further assistance is still required. We are available to support you and are committed to helping you reach a resolution.

Best Regards,
Chaithra E.

[Praful_Potphode]

Hi @barkha ,

try changing step 4 to below:

VAR CustomerTotals = 
    ADDCOLUMNS(
        SUMMARIZE(FullJoinTable, All_CustomerIDs[CustomerNo]),
        "TotalBaseQuantity", 
            VAR CurrentCustomer = [CustomerNo]
            RETURN
                SUMX(
                    FILTER(FullJoinTable, [CustomerNo] = CurrentCustomer),
                    [QuantityB]
                ),
        "TotalActualQuantity", 
            VAR CurrentCustomer = [CustomerNo]
            RETURN
                SUMX(
                    FILTER(FullJoinTable, [CustomerNo] = CurrentCustomer),
                    [QuantityA]
                )
    )

please try and let me know.

Also try summarizecolumns or groupby instead of summarize dax as it is usually recommended.

Please give kudos or mark it as solution once confirmed.

Thanks and Regards,

Praful

[barkha]

Thanks for your response. 
The solution is not working. I have tried summarizecolumns / groupby and other methods as well, not working.

[Praful_Potphode]

Hi @barkha ,

can you share the data model snapshot to understand it better.

Thanks and Regards,

Praful

[v-echaithra · Accepted solution]

Hi @Praful_Potphode ,

Please refer to the pbix below attatctched, and refer to the power query steps to get your desired output.

Hope this helps.
Thank you.

[barkha]

@Greg_Deckler 
Thanks for the response.
I have pasted a small sample input data and output. Kindly let me know if the issue is not clear.
Looking forward to your response. 

[Greg_Deckler]

@barkha Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.