Forum Discussion

barkha's avatar
barkha
Frequent Visitor
9 months ago
Solved

Mix% (Dynamic date filters, NaturalInnerjoin, Summarize)

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...
  • v-echaithra's avatar
    v-echaithra
    8 months ago

    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.

  • tamerj1's avatar
    tamerj1
    8 months ago

    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's avatar
tamerj1
Community Champion
8 months ago

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] )