Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
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
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.
Hi @Anonymous,
I don't have permission to open this link, could you provide a public link?
Best Regards,
Link
Sorry my bad , i have updated the Public link please find the same below.
https://drive.google.com/file/d/10EepFFKFEmz2KIEIuJlZ--9PW5ADGICr/view?usp=sharing
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |