Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Create Two Measures

Hi Team,

I am struggling to create two measures and I am attaching the Power BI File with Data and Measures logic/definition and expected output document in the attachment your help will be much appreciated.

Power BI File:

https://drive.google.com/file/d/1v_SCWRJev5bzC9zSzZN29uMVIH3dVWsk/view?usp=sharing

Measure definition and Output Document:

https://drive.google.com/file/d/1ALiB-eGhc7sZm5p4xf8Yeakkp5kCn34Z/view?usp=sharing

 

Content of Measure definition and Output Document:

 

Need Help

4 Replies

  • v-xulin-mstf's avatar
    v-xulin-mstf
    Community Support
    5 years ago

    Hi Anonymous,

     

    I create the measure2 as:

     

    Price Violation to Notify =

SUMX (

    ADDCOLUMNS (

        SUMMARIZE ( CostcoBasket, CostcoBasket[ProductName] ),

        "Measure2",

            VAR CostoPrice =

                MAX ( CostcoBasket[Costco_ProductPRice] )

            VAR PublixPrice =

                CALCULATE (

                    SUM ( CostcoBasket[competitor_Price] ),

                    FILTER ( CostcoBasket, CostcoBasket[Comp_Company] = "Publix" )

                )

            VAR ShopRitePrice =

                CALCULATE (

                    SUM ( CostcoBasket[competitor_Price] ),

                    FILTER ( CostcoBasket, CostcoBasket[Comp_Company] = "ShopRite" )

            

                )

            var PublixFlag=

            CALCULATE(

            MAX(CostcoBasket[Flag]),

            FILTER(

                ALL(CostcoBasket),

                CostcoBasket[Comp_Company]="Publix"

            )

            )

            var ShopRiteFlag=

             CALCULATE(

            MAX(CostcoBasket[Flag]),

            FILTER(

                ALL(CostcoBasket),

                CostcoBasket[Comp_Company]="ShopRite"

            )

            )

            var countweek=

            CALCULATE(

                COUNT(CostcoBasket[Weeks]),

                FILTER(

                    ALL(CostcoBasket),

                    PublixPrice<CostoPrice && (CostcoBasket[Weeks])<MAX(CostcoBasket[Weeks])-5 || ShopRitePrice<CostoPrice && (CostcoBasket[Weeks])<MAX(CostcoBasket[Weeks])-5

                )

                )



             RETURN

            IF(

                   IF(

                   (CostoPrice > PublixPrice || CostoPrice > ShopRitePrice ),

                  

                   OR((PublixFlag=1 && countweek>=3 || ShopRitePrice=1 && countweek>=3),(PublixFlag=0 || ShopRiteFlag=0))

                   ),

                   1, 0 )



    ),

    [Measure2]

)

     

    Here is the output:

    Here is the demo, please try it: Create Two Measures

    I’m not sure what does measure3 mean.

    How to define consecutive weeks? For example, the corresponding values for each week are “1 0 1 0 1”, does the measure return 0, or 1?

    What is the range that contains consecutive weeks? Please details.

     

    Best Regards,

    Link

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks team and  it is really helpfull .

       

      I have uploaded the Latest Power BI File where Mearue 2 is wokring for  seleceted week.

       

      https://drive.google.com/file/d/10EepFFKFEmz2KIEIuJlZ--9PW5ADGICr/view?usp=sharing 

       

      Now lets consider below output of Measure2 because Measure 3 is depedent on it.

      Measure 3: Price Violation Notify consecutive weeks count

      Scenario 1 : When i select Weeks =202036 the output of Measure3 is

      Coffebag   0  

      i will check outputs of Measure 2(0,0,0,0,0,0,0) from all available weeks(202036-202030) for Coffebag and count the latest consecutive weeks

      Grape        1  

      i will check outputs of Measure 2(1,0,0,1,1,1,0) from all available weeks(202036-202030) for Grape and count the latest consecutive weeks

      Teabag      2  

      i will check outputs of Measure 2(1,1,0,1,0,1,0) from all available weeks(202036-202030) for Teabag  and count the latest consecutive weeks

       

      Scenario 2 : When i select Weeks =202035 the output of Measure3 is

      Coffebag   0  

      i will check outputs of Measure 2(0,0,0,0,0,0) from all available weeks(202035-202030) for Coffebag and count the latest consecutive weeks

      Grape        0 

      i will check outputs of Measure 2(0,0,1,1,1,0) from all available weeks(202035-202030) for Grape and count the latest consecutive weeks

      Teabag      1

      i will check outputs of Measure 2(1,0,1,0,1,0) from all available weeks(202035-202030) for Teabag  and count the latest consecutive weeks

       

      Scenario 3 : When i select Weeks =202034 the output of Measure3 is

      Coffebag   0  

      i will check outputs of Measure 2(0,0,0,0,0) from all available weeks(202034-202030) for Coffebag and count the latest consecutive weeks

      Grape        0 

      i will check outputs of Measure 2(0,1,1,1,0) from all available weeks(202034-202030) for Grape and count the latest consecutive weeks

      Teabag      0

      i will check outputs of Measure 2(0,1,0,1,0) from all available weeks(202034-202030) for Teabag  and count the latest consecutive weeks

       

       

      Let me know if you need more information.

       

      • v-xulin-mstf's avatar
        v-xulin-mstf
        Community Support
        5 years ago

        Hi Anonymous,

         

        I don't have permission to open this link, could you provide a public link?

         

        Best Regards,

        Link