Forum Discussion

PhilSmith's avatar
PhilSmith
Icon for Helper III rankHelper III
2 years ago

HowTo put Measure in slicer or in relationship (Dynamic application)

I have a measure Speed that evaluates to "Low", Medium", or "High" for each ITEM depending on sales volume.  This can't be in a calculated table because we need to choose different sets of customers,...
Need Help
  • PhilSmith's avatar
    PhilSmith
    Icon for Helper III rankHelper III
    2 years ago
    Thank you.  I think I understand this, but something is wrong when I try to apply it.
    Lets go with the ABC problem, as it is straightforward.
     
    Sales Item Desc
    item_id Sale 1 Box
    1 $345 2 Bag
    1 $543 3 Tub
    3 $75 4 Crate
    4 $5443
    2 $543
    1 $43
     
    Item Desc
    1 Box
    2 Bag
    3 Tub
    4 Crate
     
    Related by item_id
     
    I built a measure that gives me the class, (ABC_Class_by_Item, Returns A, B or C) then I built a measure for each class that returns the class or blank.  All works as expected. The measure that "ties it all together," doesn't like Sales[ABC_Class_by_Item], (cannot be found or may not be used):
     
    ABC_Classes = 
    IF( HASONEVALUE(Sales[item_id]),
    IF(HASONEVALUE(Sales[ABC_Class_by_Item]),
    SWITCH(
              VALUES(Sales[ABC_Class_by_Item]),
              "A",[ABC_Class_A_by_Item],
              "B",[ABC_Class_A_by_Item],
    "C",[ABC_Class_C_by_Item]
    ),
    "OutOfBounds"
    )
    )
     
    The following measure is like Attendance in the example, and returns A, B, or C
     
    ABC_Class_by_Item = 
    IF (
        HASONEVALUE ( 'item'[item_id] ),
        VAR SalesByItem =
            CALCULATETABLE (
                ADDCOLUMNS (
                    SUMMARIZE ( Sales, 'item'[item_id] ),
                    "@ProdSales", [Sale]
                ),
                ALLSELECTED ( 'item' )
            )
        VAR AllSales =
            CALCULATE (
                [Sale],
                ALLSELECTED ( 'item' )
            )
        VAR CurrentSalesAmt = [Sale]
        VAR CumulatedSales =
            FILTER (
                SalesByItem,
                [@ProdSales] >= CurrentSalesAmt
            )
        VAR CumulatedSalesAmount =
            SUMX (
                CumulatedSales,
                [@ProdSales]
            )
        VAR CurrentCumulatedPct =
            DIVIDE (
                CumulatedSalesAmount,
                AllSales
            )
        VAR Result =SWITCH (
                TRUE,
                ISBLANK ( CurrentCumulatedPct ), BLANK (),
                CurrentCumulatedPct <= 0.8, "A",
                CurrentCumulatedPct <= 0.9, "B",
                "C"
            )
               RETURN
            Result
    )
     
    Individual Measures for A,B, and C that return the Class or Blank, much like Attended and Not Attended.  For instance:
    ABC_Class_A_by_Item = 
    IF (
        HASONEVALUE ( 'item'[item_id] ),
        VAR SalesByProduct =
            CALCULATETABLE (
                ADDCOLUMNS (
                    SUMMARIZE ( Sales, 'item'[item_id] ),
                    "@ProdSales", [Sale]
                ),
                ALLSELECTED ( 'item' )
            )
        VAR AllSales =
            CALCULATE (
                [Sale],
                ALLSELECTED ( 'item' )
            )
        VAR CurrentSalesAmt = [Sale]
        VAR CumulatedSales =
            FILTER (
                SalesByProduct,
                [@ProdSales] >= CurrentSalesAmt
            )
        VAR CumulatedSalesAmount =
            SUMX (
                CumulatedSales,
                [@ProdSales]
            )
        VAR CurrentCumulatedPct =
            DIVIDE (
                CumulatedSalesAmount,
                AllSales
            )
        VAR Result =SWITCH (
                TRUE,
                ISBLANK ( CurrentCumulatedPct ), BLANK (),
                CurrentCumulatedPct <= 0.8, "A",
            BLANK()   )
           
        RETURN
            Result
    )
     
    Any ideas why?