Forum Discussion

afuri's avatar
afuri
Frequent Visitor
3 years ago
Solved

compile discounts on master data based on matrix

Hello Guys I have two tables: table_1 --> this table contains the manual mapping of a discount percentage. The mapping depends on the links entered in the table (maximum priority starting from the ...
  • scee07's avatar
    scee07
    3 years ago

    Hi, 

    here is my next approach. As discussed I constructed all relevant IDs. Logic as follows:
    - if an attribute has no specific value "XXXX" is set in order to avoid the empty/blank problem 
    - then we have IDs of the form SET-CAT-FAM-GRU with A-XXXX-ABC-DE and the like.
    - then an id 1111 means all attributes have a value and there are no blanks, e.g. 1000 means only the SET is non-blank and so on. 
    - in this terminology there are GRU discounts that have 1xx1 signature
    - FAM discounts have a  1x1x signature and so forth
    TBL2 gets the calculated columns as follows:

    GRUDiscount =
VAR thisSET = TBL2[SET]
VAR thisFAM = TBL2[FAM]
VAR thisCAT = TBL2[CAT]
VAR thisGRU = TBL2[GRU]
VAR hasGRU =
    IF ( ISBLANK ( thisGRU ) = FALSE () && thisGRU <> "", TRUE (), FALSE () )
VAR hasSET =
    IF ( ISBLANK ( thisSET ) = FALSE () && thisSET <> "", TRUE (), FALSE () )
VAR hasCAT =
    IF ( ISBLANK ( thisCAT ) = FALSE () && thisCAT <> "", TRUE (), FALSE () )
VAR hasFAM =
    IF ( ISBLANK ( thisFAM ) = FALSE () && thisFAM <> "", TRUE (), FALSE () ) //GRU IDs 
/// 1111
VAR ID_1111 =
    IF (
        hasCAT && hasGRU
            && hasFAM,
        COMBINEVALUES ( "-", thisSET, thisCAT, thisFAM, thisGRU ),
        "None"
    ) //  1001
VAR ID_1001 =
    IF ( hasGRU, COMBINEVALUES ( "-", thisSET, "XXXX", "XXXX", thisGRU ), "None" ) // 1101 
VAR ID_1101 =
    IF (
        hasCAT && hasGRU,
        COMBINEVALUES ( "-", thisSET, thisCAT, "XXXX", thisGRU ),
        "None"
    ) // 1011
VAR ID_1011 =
    IF (
        hasGRU && hasFAM,
        COMBINEVALUES ( "-", thisSET, "XXXX", thisFAM, thisGRU ),
        "None"
    )
RETURN
    CALCULATE (
        FIRSTNONBLANK ( TBL1[VAL], 0 ),
        FILTER (
            TBL1,
            TBL1[ID] == ID_1111
                || TBL1[ID] == ID_1001
                || TBL1[ID] == ID_1101
                || TBL1[ID] == ID_1011
                && TBL1[VAL] <> 0
        )
    )

FAMDiscount =
VAR thisSET = TBL2[SET]
VAR thisFAM = TBL2[FAM]
VAR thisCAT = TBL2[CAT]
VAR thisGRU = TBL2[GRU]
VAR hasGRU =
    IF ( ISBLANK ( thisGRU ) = FALSE () && thisGRU <> "", TRUE (), FALSE () )
VAR hasSET =
    IF ( ISBLANK ( thisSET ) = FALSE () && thisSET <> "", TRUE (), FALSE () )
VAR hasCAT =
    IF ( ISBLANK ( thisCAT ) = FALSE () && thisCAT <> "", TRUE (), FALSE () )
VAR hasFAM =
    IF ( ISBLANK ( thisFAM ) = FALSE () && thisFAM <> "", TRUE (), FALSE () ) //FAM IDs 
/// 1110
VAR ID_1110 =
    IF (
        hasCAT && hasFAM,
        COMBINEVALUES ( "-", thisSET, thisCAT, thisFAM, "XXXX" ),
        "None"
    ) //  1010
VAR ID_1010 =
    IF ( hasFAM, COMBINEVALUES ( "-", thisSET, "XXXX", thisFAM, "XXXX" ), "None" )
RETURN
    CALCULATE (
        FIRSTNONBLANK ( TBL1[VAL], 0 ),
        FILTER ( TBL1, TBL1[ID] == ID_1110 || TBL1[ID] == ID_1010 && TBL1[VAL] <> 0 )
    )
CATDiscount = 
VAR thisSET = TBL2[SET]
VAR thisFAM = TBL2[FAM]
VAR thisCAT = TBL2[CAT]
VAR thisGRU = TBL2[GRU] 
VAR hasGRU = if(ISBLANK(thisGRU) = FALSE() && thisGRU <> "", TRUE(), FALSE())
VAR hasSET = if(ISBLANK(thisSET) = FALSE() && thisSET <> "", TRUE(), FALSE())
VAR hasCAT = if(ISBLANK(thisCAT) = FALSE() && thisCAT <> "", TRUE(), FALSE())
VAR hasFAM = if(ISBLANK(thisFAM) = FALSE() && thisFAM <> "", TRUE(), FALSE())

//CAT IDs 
/// 1100
VAR ID_1100 = if(hasCAT , COMBINEVALUES("-", thisSET, thisCAT, "XXXX", "XXXX"), "None")

return 
CALCULATE(FIRSTNONBLANK(TBL1[VAL],0), FILTER(TBL1, TBL1[ID] == ID_1100 && TBL1[VAL] <> 0))

SETDiscount =
VAR thisSET = TBL2[SET]
VAR thisFAM = TBL2[FAM]
VAR thisCAT = TBL2[CAT]
VAR thisGRU = TBL2[GRU]
VAR hasGRU =
    IF ( ISBLANK ( thisGRU ) = FALSE () && thisGRU <> "", TRUE (), FALSE () )
VAR hasSET =
    IF ( ISBLANK ( thisSET ) = FALSE () && thisSET <> "", TRUE (), FALSE () )
VAR hasCAT =
    IF ( ISBLANK ( thisCAT ) = FALSE () && thisCAT <> "", TRUE (), FALSE () )
VAR hasFAM =
    IF ( ISBLANK ( thisFAM ) = FALSE () && thisFAM <> "", TRUE (), FALSE () ) //SET IDs 
/// 1000
VAR ID_1000 =
    COMBINEVALUES ( "-", thisSET, "XXXX", "XXXX", "XXXX" )
RETURN
    CALCULATE (
        FIRSTNONBLANK ( TBL1[VAL], 0 ),
        FILTER ( TBL1, TBL1[ID] == ID_1000 && TBL1[VAL] <> 0 )
    )
Discount =
IF (
    TBL2[GRUDiscount] <> 0,
    TBL2[GRUDiscount],
    IF (
        TBL2[FAMDiscount] <> 0,
        TBL2[FAMDiscount],
        IF ( TBL2[CATDiscount] <> 0, TBL2[CATDiscount], TBL2[SETDiscount] )
    )
)


    Basically, all cases are intended to be covered. In case there are multiple discounts, the most specific is applied. 
    Assumption is there is only one discount? If not firstnonblank can be replaced by sum and all discounts could be summed up (SET, CAT, ...)

     

    The discount for SKU10 is now assigned. Here the updated file:
    Discount.2.0.pbix
    This is only tested in the sense that it reproduces the discounts for the 10 SKUs. You might test it further to find bugs. More importantly, it would be good to have a common understanding that this is one way to approach the problem. We can touch base on Monday, if necessary. 
    Best regards 
    Christian


scee07's avatar
scee07
Icon for Resolver I rankResolver I
3 years ago

Hi, 
ID will not work as you look for strings contained in a string. E.g. set B has the requirement that CAT is FF. For an ID that only means that the ID contains B and FF. 
I will just sketch a calculated column for Table2 to express how I would do it  

Discount =
VAR thisSET = Table2[SET]
VAR thisFAM = Table2[FAM]
VAR thisCAT = Table2[CAT]
VAR thisGRU = Table2[GRU] // the combination of SKU and SET is unique: the SKU is unique and in only 1 set
VAR necessaryFAM =
    CALCULATE (
        FIRSTNONBLANK ( Table1[FAM], 0 ),
        FILTER ( Table1, Table1[SET] = thisSET )
    )
VAR necessaryCAT =
    CALCULATE (
        FIRSTNONBLANK ( Table1[CAT], 0 ),
        FILTER ( Table1, Table1[SET] = thisSET )
    )
VAR necessaryGRU =
    CALCULATE (
        FIRSTNONBLANK ( Table1[GRU], 0 ),
        FILTER ( Table1, Table1[SET] = thisSET )
    )
VAR discount =
    CALCULATE (
        FIRSTNONBLANK ( Table1[VAL], 0 ),
        FILTER ( Table1, Table1[SET] = thisSET )
    )
VAR hasDiscount =
    (
        thisFAM
            == necessaryFAM || ISBLANK ( necessaryFAM )
    )
        && (
            thisCAT
                == necessaryFAM || ISBLANK ( necessaryCAT )
        )
        && (
            thisGRU
                == necessaryGRU || ISBLANK ( necessaryGRU )
        )
RETURN
    IF ( hasDiscount, discount )

Basically you get the necessary values from the discount table. The you say for every classifying column: either my current value matches or the required value is blank. Then in hasDiscount you check if all conditions are met. If yes, return the discount, if not return blank.
Not tested, as I have no time. But if you get the gist of it you be able to assign your discounts.
Best regards 
Christian

  • afuri's avatar
    afuri
    Frequent Visitor
    3 years ago

    Hi Christian,

     

    thanks for the proposed solution, i wrote the code but i get wrong values

    I fixed this part:

     

     

    ...... 
            thisCAT
                == necessaryCAT || ISBLANK(necessaryCAT)
            )
......

     

     

     

    the column works but only reads the first combination, for example in the following case (SET F):

     

    SETCATFAMGRUVAL
    FLGEFTH 5,00
    FLGEFTJ 5,00
    FLGEFTL 7,00
    FLGEFTM 2,00
    FLGEFVI 4,00

     

    The result is ok only for   SET = F + CAT = LGE + FAM = FTH

     

    for others combinations result is BLANK

    ...

     

    • scee07's avatar
      scee07
      Icon for Resolver I rankResolver I
      3 years ago

      Hi, 

      as I said not tested. Will find time tomorrow to set this up with a toy model and come back to you. 
      In principle this should work. But as always it has to be implemented to see the bugs.
      Best regards 
      Christian

      • afuri's avatar
        afuri
        Frequent Visitor
        3 years ago

        You are right !
        thanks for the precious help,

        see you tomorrow