table 'Measure THC' lineageTag: 79f899a5-acf7-4122-a97e-2b505875bc6f measure 'External.△MS%.KPI_THC' = VAR CV = [External.MS%.KPI_THC] VAR PV = [External.MS%.LY.KPI_THC] RETURN SWITCH(TRUE(), ISFILTERED(Dim_DataSource) && "AIA" IN VALUES(Dim_DataSource[SOURCENAME]) && PV <> BLANK(), CV - PV, ISFILTERED(Dim_DataSource) && "AIA" IN VALUES(Dim_DataSource[SOURCENAME]),BLANK(), CV - PV) formatString: 0.0%;-0.0%;0.0% displayFolder: 01.Sales lineageTag: 044e73ad-258c-401b-b885-2495cc8f8e39 measure 'External.△MS%.KPI.Mol_THC' = VAR CV = [External.MS%.KPI.Mol_THC] VAR PV = DIVIDE([External.Sales.LY.KPI_THC6],[External.Sales.LY.KPI.Mol_THC]) // [External.MS%.LY.KPI.Mol] RETURN SWITCH(TRUE(), ISFILTERED(Dim_DataSource) && "AIA" IN VALUES(Dim_DataSource[SOURCENAME]) && PV <> BLANK(), CV - PV, ISFILTERED(Dim_DataSource) && "AIA" IN VALUES(Dim_DataSource[SOURCENAME]),BLANK(), CV - PV) formatString: 0.0%;-0.0%;0.0% displayFolder: 01.Sales lineageTag: 2647c218-b3d3-499e-94ef-2aa2cfd66033 measure 'External.EI.KPI_THC' = DIVIDE ( DIVIDE ( [External.Sales.KPI_THC6], [External.Sales.LY.KPI_THC6] ), DIVIDE ( [External.Sales.KPI.Market_THC2], [External.Sales.LY.KPI.Market_THC2] ) ) formatString: #,0.00 displayFolder: 01.Sales lineageTag: 0463e7d5-6109-4e2d-8599-fb31cc5d9096 measure 'External.Gr%.KPI_THC' = VAR CV = [External.Sales.KPI_THC6] VAR PV = [External.Sales.LY.KPI_THC6] RETURN IF(CV <> BLANK(),DIVIDE(CV - PV,PV)) formatString: 0.0%;-0.0%;0.0% displayFolder: 01.Sales lineageTag: 6df78dde-93ee-458e-b7ba-82f3c326dfaa measure 'External.Gr%.KPI.Mol_THC' = DIVIDE([External.Sales.KPI.Mol_THC]-[External.Sales.LY.KPI.Mol_THC],[External.Sales.LY.KPI.Mol_THC]) formatString: 0.0%;-0.0%;0.0% displayFolder: 01.Sales lineageTag: 9cc0c25a-ea72-423c-8420-58b363c2bedf measure 'External.GRSales.KPI_THC' = [External.Sales.KPI_THC6]-[External.Sales.LY.KPI_THC6] formatString: #,0.00 displayFolder: 01.Sales lineageTag: c9095fb7-c0d5-4fcd-a106-9294e9b44200 measure 'External.GRSales.KPI.Market_THC' = [External.Sales.KPI.Market_THC2]-[External.Sales.LY.KPI.Market_THC2] displayFolder: 01.Sales lineageTag: a43fbf29-ecc2-4725-af77-d7a1c892f6d0 measure 'External.GRSalesContr%.KPI_THC' = DIVIDE([External.GRSales.KPI_THC],[External.GRSales.KPI.Market_THC]) formatString: 0.0%;-0.0%;0.0% displayFolder: 01.Sales lineageTag: 26686939-f37c-4ed7-a09c-b7a8c1c5defe measure 'External.HoHGR%.KPI_THC' = VAR CV = CALCULATE([External.Sales.KPI_THC6], MQY[Period]="HYTDAvg") VAR PV = CALCULATE([External.Sales.KPI_THC6], MQY[Period]="RHYAvg") RETURN IF(PV <> BLANK(),DIVIDE(CV-PV,PV)) formatString: 0.0%;-0.0%;0.0% displayFolder: 01.Sales lineageTag: 6cc3edb2-1747-434c-99e1-3f2464cb19ee measure 'External.MS%.KPI_THC' = DIVIDE([External.Sales.KPI_THC6],[External.Sales.KPI.Market_THC2]) formatString: 0.0%;-0.0%;0.0% displayFolder: 01.Sales lineageTag: 461535b6-3f7a-4172-b7eb-8583ae56f841 measure 'External.MS%.KPI.Mol_THC' = DIVIDE([External.Sales.KPI_THC6],[External.Sales.KPI.Mol_THC]) formatString: 0.0%;-0.0%;0.0% displayFolder: 01.Sales lineageTag: 9ef9982f-bdd4-417f-b4dd-c49679715a09 measure 'External.MS%.LY.KPI_THC' = DIVIDE([External.Sales.LY.KPI_THC6],[External.Sales.LY.KPI.Market_THC2]) displayFolder: 01.Sales lineageTag: b8508ee9-efb3-4afb-9e7a-e6d8c6f1f81a measure 'External.RQGR%.KPI_THC' = VAR CV = CALCULATE([External.Sales.KPI_THC6], MQY[Period]="QTDAvg") VAR PV = CALCULATE(CALCULATE([External.Sales.KPI_THC6], PREVIOUSQUARTER(ENDOFQUARTER(Dim_Calendar[Date]))), MQY[Period]="QTDAvg") RETURN IF(PV <> BLANK(), DIVIDE(CV-PV,PV)) formatString: 0.0%;-0.0%;0.0% displayFolder: 01.Sales lineageTag: 5e1c046b-aa7c-4de9-8e77-e5650a231ab0 measure 'External.Sales.KPI_THC6' = [External.Sales.MTH.MID_THC5] formatString: #,0 displayFolder: 01.Sales lineageTag: 85292bae-6ed5-4eb5-9314-01166ff2f65e measure 'External.Sales.KPI.Mol_THC' = IF ( ISFILTERED ( Dim_Market ) || ISFILTERED ( Dim_MarketTA ) || ISFILTERED ( Dim_BrandDefaultMKT ), IF ( [External.Sales.KPI.Market_THC2] <> 0, VAR MKT = MAX(Dim_Market[MKT_SOURCE]) VAR CMPS = CALCULATETABLE(VALUES ( Dim_MKTPackMapping[MOLECULE_SOURCE] ),Dim_MKTPackMapping[MKT_SOURCE] = MKT) RETURN CALCULATE ( [External.Sales.KPI_THC6], REMOVEFILTERS ( Dim_PackInfo[PROD_DES] ), REMOVEFILTERS ( Dim_PackInfo[PROD_DES_C] ), REMOVEFILTERS ( Dim_PackInfo[PROD_COD] ), REMOVEFILTERS ( Dim_PackInfo[CORP_COD] ), REMOVEFILTERS ( Dim_PackInfo[CORP_DES] ), REMOVEFILTERS ( Dim_PackInfo[CORP_DES_C] ), REMOVEFILTERS ( Dim_PackInfo[CORP_TYPE] ), REMOVEFILTERS ( Dim_PackInfo[PROD_CORP] ), REMOVEFILTERS ( Dim_PackInfo[PROD_CORP_C] ), ALL ( Dim_Corp ), ALLEXCEPT ( Dim_PackInfo, Dim_PackInfo[CMPS_DES], Dim_PackInfo[CMPS_DES_C], Dim_PackInfo[MOLECULE_CODE_DESC] ), KEEPFILTERS ( Dim_Market[Market] <> BLANK () ) , TREATAS ( VALUES ( Dim_MKTPackMapping[MOLECULE_SOURCE] ), Fact_Sales_THC[MOLECULE_SOURCE] ) ) ) ) formatString: #,0.00 displayFolder: 01.Sales lineageTag: d5959e57-41bc-43f4-b923-984bcaa5d28d measure 'External.Sales.LY.KPI.Mol_THC' = IF ( ISFILTERED ( Dim_Market ) || ISFILTERED ( Dim_MarketTA ) || ISFILTERED ( Dim_BrandDefaultMKT ), IF ( [External.Sales.LY.KPI.Market_THC2] <> 0, VAR MKT = MAX(Dim_Market[MKT_SOURCE]) VAR CMPS = CALCULATETABLE(VALUES ( Dim_MKTPackMapping[MOLECULE_SOURCE] ),Dim_MKTPackMapping[MKT_SOURCE] = MKT) RETURN CALCULATE ( [External.Sales.LY.KPI_THC6], REMOVEFILTERS ( Dim_PackInfo[PROD_DES] ), REMOVEFILTERS ( Dim_PackInfo[PROD_DES_C] ), REMOVEFILTERS ( Dim_PackInfo[PROD_COD] ), REMOVEFILTERS ( Dim_PackInfo[CORP_COD] ), REMOVEFILTERS ( Dim_PackInfo[CORP_DES] ), REMOVEFILTERS ( Dim_PackInfo[CORP_DES_C] ), REMOVEFILTERS ( Dim_PackInfo[CORP_TYPE] ), REMOVEFILTERS ( Dim_PackInfo[PROD_CORP] ), REMOVEFILTERS ( Dim_PackInfo[PROD_CORP_C] ), ALL ( Dim_Corp ), ALLEXCEPT ( Dim_PackInfo, Dim_PackInfo[CMPS_DES], Dim_PackInfo[CMPS_DES_C], Dim_PackInfo[MOLECULE_CODE_DESC] ), KEEPFILTERS ( Dim_Market[Market] <> BLANK () ), TREATAS ( VALUES ( Dim_MKTPackMapping[MOLECULE_SOURCE] ), Fact_Sales_THC[MOLECULE_SOURCE] ) ) ) ) formatString: #,0.00 displayFolder: 01.Sales lineageTag: bf39d891-4811-4dbf-bdb3-787971f27225 measure 'External.Sales.MTH_THC_4' = VAR SalesKPI = SWITCH ( [External.SelectUnit], "RMB", [External.Value.MTH_THC_3], "K RMB", DIVIDE ( [External.Value.MTH_THC_3], 1000 ), "M RMB", DIVIDE ( [External.Value.MTH_THC_3], 1000000 ), "USD", DIVIDE ( [External.Value.MTH_THC_3], [ExchangeRate] ), "K USD", DIVIDE ( DIVIDE ( [External.Value.MTH_THC_3], [ExchangeRate] ), 1000 ), "M USD", DIVIDE ( DIVIDE ( [External.Value.MTH_THC_3], [ExchangeRate] ), 1000000 ), "Unit", [External.Unit.MTH_THC3], "K Unit", DIVIDE ( [External.Unit.MTH_THC3], 1000 ), "M Unit", DIVIDE ( [External.Unit.MTH_THC3], 1000000 ), "CU", [External.CountingUnit.MTH_THC3], "K CU", DIVIDE ( [External.CountingUnit.MTH_THC3], 1000 ), "M CU", DIVIDE ( [External.CountingUnit.MTH_THC3], 1000000 ), "PDOT", [External.PDOT.MTH_THC3], "K PDOT", DIVIDE ( [External.PDOT.MTH_THC3], 1000 ), "M PDOT", DIVIDE ( [External.PDOT.MTH_THC3], 1000000 ), "张数", [External.Prescription.MTH _THC1] // "K Rx", DIVIDE ( [External.Prescription.MTH], 1000 ), // "M Rx", DIVIDE ( [External.Prescription.MTH], 1000000 ) ) VAR Channel = ALLSELECTED (Dim_DataSource[SOURCENAME]) VAR IsCounty = "County" IN Channel VAR IsCHC = "CHC" IN Channel VAR IsTHC = "THC" IN Channel VAR Prov = SELECTEDVALUE(Dim_GeoGroup[PROVINCE_GROUP]) RETURN SWITCH ( TRUE (), IsCounty = FALSE () && Prov IN {"BBU COUNTY","OBU COUNTY","BBU_BU_COUNTY","OBU_BU_COUNTY","OBU East5","OBU NonEast5","OBU EAST5","OBU NONEAST5"}, BLANK (), IsCHC = FALSE () && Prov = "CHC13城市", BLANK (), IsTHC = FALSE () && Prov = "THC14省", BLANK (), SalesKPI ) displayFolder: 01.Sales lineageTag: 5dc6d317-0dec-4f3d-a61b-4323fefa266e measure 'External.Sales.MTH.MID_THC5' = ``` VAR DS = SELECTEDVALUE ( Dim_DataSource[SOURCENAME] ) VAR DS1 = SELECTEDVALUE(Dim_DataSource[DATA_SOURCE]) VAR MKT_FLAG = SWITCH(TRUE(), ISFILTERED(Dim_Market), MAX(Dim_Market[MKT_FLAG]), ISFILTERED(Dim_BrandDefaultMKT), CALCULATE(MAX(Dim_BrandDefaultMKT[MKT_FLAG]),Dim_BrandDefaultMKT[DATA_SOURCE] = DS1), 0) VAR PROD_FLAG = SWITCH(TRUE(), ISFILTERED(Dim_Market), MAX(Dim_Market[PRD_FLAG]), ISFILTERED(Dim_BrandDefaultMKT), CALCULATE(MAX(Dim_BrandDefaultMKT[PRD_FLAG]),Dim_BrandDefaultMKT[DATA_SOURCE] = DS1), 0) RETURN SWITCH ( TRUE (), //若选择Molecule,选择地理，且市场是品牌报告里面的市场 --ISFILTERED(Dim_IsTarget) && // NOT ISFILTERED(Dim_Market) && NOT ISFILTERED(Dim_BrandDefaultMKT),BLANK(), ISFILTERED(Dim_CoreDept) && NOT ISFILTERED(Dim_Market) && NOT ISFILTERED(Dim_BrandDefaultMKT),BLANK(), DS IN {"Retail","EC"} && MKT_FLAG = 1 && NOT ISFILTERED ( Dim_PackInfo[PROD_COD] ) && NOT ISFILTERED ( Dim_PackInfo[PROD_DES_C] ) && NOT ISFILTERED ( Dim_PackInfo[PROD_DES] ) && NOT ISFILTERED ( Dim_PackInfo[PROD_CORP] ) && NOT ISFILTERED ( Dim_PackInfo[PACK_COD] ) && NOT ISFILTERED ( Dim_PackInfo[PROD_CORP_C] ) && NOT ISFILTERED ( Dim_PackInfo[PROD_PACK_CORP] ) && NOT ISFILTERED ( Dim_PackInfo[PROD_PACK] ) && NOT ISFILTERED ( Dim_PackInfo[PACK_DES] ) && NOT ISFILTERED ( Dim_PackInfo[PACK_LCH] ) && ISFILTERED(Dim_Geo) && ( ISFILTERED ( Dim_PackInfo[MOLECULE_CODE_DESC] ) || ISFILTERED ( Dim_PackInfo[MOLECULE_DES_C] ) ||ISFILTERED ( Dim_PackInfo[CMPS_COD] ) || ISFILTERED ( Dim_PackInfo[CMPS_DES] ) || ISFILTERED ( Dim_PackInfo[CMPS_DES_C] )), [External.Sales.MTH_THC_4], //除开特定市场外，其余的retail，针对于class，选择地理或org维度，值为空 DS = "Retail" && (ISFILTERED(Dim_Geo) || ISFILTERED(Dim_Org)) && ISFILTERED(Dim_Class) && (ISFILTERED(Dim_Market) || ISFILTERED(Dim_BrandDefaultMKT)) && MKT_FLAG = 0,BLANK(), //选择市场，只出现品牌报告里面的有市场； DS = "Retail" && (ISFILTERED(Dim_Geo) || ISFILTERED(Dim_Org)) && (ISFILTERED(Dim_Market) || ISFILTERED(Dim_BrandDefaultMKT)) && PROD_FLAG = 0,BLANK(), --若不选pack，用于计算class相关KPI DS IN {"Retail","EC"} && (ISFILTERED ( Dim_Geo ) || ISFILTERED ( Dim_Org )) && NOT ISFILTERED(Dim_PackInfo), [External.Sales.MTH_THC_4], //若选择Molecule,没选择地理 DS IN {"Retail","EC"} && NOT ISFILTERED(Dim_Geo) && NOT ISFILTERED(Dim_Org) && NOT ISFILTERED ( Dim_PackInfo[PROD_COD] ) && NOT ISFILTERED ( Dim_PackInfo[PROD_DES_C] ) && NOT ISFILTERED ( Dim_PackInfo[PROD_DES] ) && NOT ISFILTERED ( Dim_PackInfo[PROD_CORP] ) && NOT ISFILTERED ( Dim_PackInfo[PACK_COD] ) && NOT ISFILTERED ( Dim_PackInfo[PROD_CORP_C] ) && NOT ISFILTERED ( Dim_PackInfo[PROD_PACK_CORP] ) && NOT ISFILTERED ( Dim_PackInfo[PROD_PACK] ) && NOT ISFILTERED ( Dim_PackInfo[PACK_DES] ) && NOT ISFILTERED ( Dim_PackInfo[PACK_LCH] ) && ( ISFILTERED ( Dim_PackInfo[MOLECULE_CODE_DESC] ) || ISFILTERED ( Dim_PackInfo[MOLECULE_DES_C] ) ||ISFILTERED ( Dim_PackInfo[CMPS_COD] ) || ISFILTERED ( Dim_PackInfo[CMPS_DES] ) || ISFILTERED ( Dim_PackInfo[CMPS_DES_C] )), [External.Sales.MTH_THC_4], //若选择Molecule,选择地理 DS IN {"Retail","EC"} && NOT ISFILTERED ( Dim_PackInfo[PROD_COD] ) && NOT ISFILTERED ( Dim_PackInfo[PROD_DES_C] ) && NOT ISFILTERED ( Dim_PackInfo[PROD_DES] ) && NOT ISFILTERED ( Dim_PackInfo[PROD_CORP] ) && NOT ISFILTERED ( Dim_PackInfo[PACK_COD] ) && NOT ISFILTERED ( Dim_PackInfo[PROD_CORP_C] ) && NOT ISFILTERED ( Dim_PackInfo[PROD_PACK_CORP] ) && NOT ISFILTERED ( Dim_PackInfo[PROD_PACK] ) && NOT ISFILTERED ( Dim_PackInfo[PACK_DES] ) && NOT ISFILTERED ( Dim_PackInfo[PACK_LCH] ) && ( ISFILTERED ( Dim_PackInfo[MOLECULE_CODE_DESC] ) || ISFILTERED ( Dim_PackInfo[MOLECULE_DES_C] ) ||ISFILTERED ( Dim_PackInfo[CMPS_COD] ) || ISFILTERED ( Dim_PackInfo[CMPS_DES] ) || ISFILTERED ( Dim_PackInfo[CMPS_DES_C] )), CALCULATE ( [External.Sales.MTH_THC_4] // , // Fact_Sales_THC[CMPS_FLAG] = 1 ), //若选择BRAND,没有选择地理 DS IN {"Retail","EC"} && NOT ISFILTERED ( Dim_Geo ) && NOT ISFILTERED ( Dim_Org ) && NOT ISFILTERED ( Dim_PackInfo[PACK_COD] ) && NOT ISFILTERED ( Dim_PackInfo[PROD_PACK_CORP] ) && NOT ISFILTERED ( Dim_PackInfo[PACK_DES] ) && NOT ISFILTERED ( Dim_PackInfo[PACK_LCH] ) && (ISFILTERED ( Dim_PackInfo[PROD_COD] ) || ISFILTERED ( Dim_PackInfo[PROD_CORP_C] ) || ISFILTERED ( Dim_PackInfo[PROD_DES] ) || ISFILTERED ( Dim_PackInfo[PROD_DES_C] )), CALCULATE ( [External.Sales.MTH_THC_4], Fact_Sales_THC[PROD_FLAG] IN {1, 2} ), //若选择BRAND,选择地理 DS IN {"Retail","EC"} && NOT ISFILTERED ( Dim_PackInfo[PACK_COD] ) && NOT ISFILTERED ( Dim_PackInfo[PROD_PACK_CORP] ) && NOT ISFILTERED ( Dim_PackInfo[PACK_DES] ) && NOT ISFILTERED ( Dim_PackInfo[PACK_LCH] ) && (ISFILTERED ( Dim_PackInfo[PROD_COD] ) || ISFILTERED ( Dim_PackInfo[PROD_CORP_C] ) || ISFILTERED ( Dim_PackInfo[PROD_DES] ) || ISFILTERED ( Dim_PackInfo[PROD_DES_C] )), CALCULATE ( [External.Sales.MTH_THC_4] // , // Fact_Sales_THC[GEO_FLAG] = 1 ), //若选择PACKAGE,不选择地理维度，1表示pack+省份数据；2表示pack+全国的数据 DS IN {"Retail","EC"} && NOT ISFILTERED ( Dim_Org ) && NOT ISFILTERED ( Dim_Geo ) && (ISFILTERED ( Dim_PackInfo[PACK_DES] ) || ISFILTERED ( Dim_PackInfo[PACK_LCH] ) || ISFILTERED ( Dim_PackInfo[PACK_COD] ) || ISFILTERED ( Dim_PackInfo[PROD_PACK_CORP] )), CALCULATE ( [External.Sales.MTH_THC_4] // , // Fact_Sales_THC[PACK_FLAG] IN { 1, 2 } ), //若选择PACKAGE,且省份维度不是全国 DS IN {"Retail","EC"} && (ISFILTERED ( Dim_PackInfo[PACK_DES] ) || ISFILTERED ( Dim_PackInfo[PACK_LCH] ) || ISFILTERED ( Dim_PackInfo[PACK_COD] ) || ISFILTERED ( Dim_PackInfo[PROD_PACK_CORP] )), CALCULATE ( [External.Sales.MTH_THC_4] // , // Fact_Sales_THC[PACK_FLAG] = 1 ), [External.Sales.MTH_THC_4] ) ``` displayFolder: 01.Sales lineageTag: 421161f4-4f95-402e-bce9-48ea2e5f0eb9 annotation PBI_FormatHint = {"isGeneralNumber":true} measure 'External.SalesContr%.KPI_THC' = DIVIDE([External.Sales.KPI_THC6],CALCULATE([External.Sales.KPI_THC6],ALLSELECTED())) formatString: 0.0%;-0.0%;0.0% displayFolder: 01.Sales lineageTag: cdff6422-b544-415e-82a6-982bfaa44048 measure 'External.Value.MID_THC_1' = ``` VAR MKT = VALUES(Dim_Market[MKT_SOURCE]) VAR AUDIT = CALCULATETABLE(VALUES(Dim_RC[AUDIT_SOURCE]),Dim_RC[MKT_SOURCE] IN MKT) // VAR INST = CALCULATETABLE(VALUES(Dim_IsTarget[INST_SOURCE]),Dim_IsTarget[MKT_SOURCE] IN MKT) VAR DEPT = CALCULATETABLE(VALUES(Dim_CoreDept[DEPT_SOURCE]),Dim_CoreDept[MKT_SOURCE] IN MKT) RETURN SWITCH ( TRUE (), ( ISFILTERED ( Dim_Market ) || ISFILTERED ( Dim_BrandDefaultMKT ) ) && ISFILTERED ( Dim_Org ), CALCULATE( SUMX ( Fact_Sales_THC, Fact_Sales_THC[SALES_VALUE_CAL] * [External.SelectValueMKTRatio] * [External.SelectRegionMKTRatio] ), TREATAS(AUDIT,Dim_GeoKey[AUDIT_SOURCE]) ), ( ISFILTERED ( Dim_Market ) || ISFILTERED ( Dim_BrandDefaultMKT ) ) && (--ISFILTERED ( Dim_IsTarget ) || ISFILTERED(Dim_IsTarget_AIASpecial)|| ISFILTERED(Dim_HP_Potential_AIA)), CALCULATE( SUMX ( Fact_Sales_THC, Fact_Sales_THC[SALES_VALUE_CAL] * [External.SelectValueMKTRatio] ) // , // TREATAS(INST,Dim_Institution[INST_SOURCE]) ), ( ISFILTERED ( Dim_Market ) || ISFILTERED ( Dim_BrandDefaultMKT ) ) && ISFILTERED ( Dim_CoreDept ), CALCULATE( SUMX ( Fact_Sales_THC, Fact_Sales_THC[SALES_VALUE_CAL] * [External.SelectValueMKTRatio] ), TREATAS(DEPT,Fact_Sales[DEPT_SOURCE]) ), ( ISFILTERED ( Dim_Market ) || ISFILTERED ( Dim_BrandDefaultMKT ) || ISFILTERED ( Dim_MarketTA ) || ISFILTERED(Dim_Class)), SUMX ( Fact_Sales_THC, Fact_Sales_THC[SALES_VALUE_CAL] * [External.SelectValueMKTRatio] ), SUMX ( Fact_Sales_THC, [SALES_VALUE_CAL] ) ) ``` displayFolder: 01.Sales lineageTag: fdcd3afa-2ee8-4e11-ac24-f0062837b5d5 annotation PBI_FormatHint = {"isGeneralNumber":true} measure 'External.Value.MTH_THC_3' = ``` VAR TA = SELECTEDVALUE ( Dim_MarketTA[TA] ) VAR DS = SELECTEDVALUE ( Dim_DataSource[SOURCENAME] ) VAR MKTTA = MAX(Dim_Market[TA]) RETURN SWITCH ( TRUE (), //Retail 有一个TA是单独的，买的DTP数据 DS = "Retail" && ISFILTERED(Dim_MarketTA) && TA = "ONCO", CALCULATE ( [External.Value.MTH.MID_THC_2] --, Fact_Sales[DTP_FLAG] = 1 ), DS = "Retail" && MKTTA = "ONCO", CALCULATE ( [External.Value.MTH.MID_THC_2] --, Fact_Sales[DTP_FLAG] = 1 ), // //若选择PACKAGE,不选择地理维度，1表示pac1+省份数据；2表示pack+全国的数据 // DS = "Retail(Quarterly)" // && ( // ISFILTERED ( TD_EXTERNAL_PACKINFO[PACK_DES] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PROD_PACK] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PACK_COD] ) // ) // && NOT ISFILTERED ( TD_EXTERNAL_GEO_Analysis ) // && NOT ISFILTERED ( TD_EXTERNAL_GEO_MID ), // CALCULATE ( // [External.Value.MTH.MID], // TF_EXTERNAL_SALES[PACK_FLAG] IN { 1, 2 }, // TF_EXTERNAL_SALES[DTP_FLAG] = 0 // ), // //若选择PACKAGE,且省份维度是全国 // DS = "Retail(Quarterly)" // && ( // ISFILTERED ( TD_EXTERNAL_PACKINFO[PACK_DES] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PROD_PACK] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PACK_COD] ) // ) // && SELECTEDVALUE ( TD_EXTERNAL_GEO_Analysis[PROVINCE_C] ) = "全国", // CALCULATE ( // [External.Value.MTH.MID], // TF_EXTERNAL_SALES[PACK_FLAG] IN { 1, 2 }, // TF_EXTERNAL_SALES[DTP_FLAG] = 0 // ), // //若选择PACKAGE,且省份维度不是全国 // DS = "Retail(Quarterly)" // && ( // ISFILTERED ( TD_EXTERNAL_PACKINFO[PACK_DES] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PROD_PACK] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PACK_COD] ) // ) // && SELECTEDVALUE ( TD_EXTERNAL_GEO_Analysis[PROVINCE_C] ) <> "全国", // CALCULATE ( // [External.Value.MTH.MID], // TF_EXTERNAL_SALES[PACK_FLAG] = 1, // TF_EXTERNAL_SALES[DTP_FLAG] = 0 // ), // //若选择BRAND, // DS = "Retail(Quarterly)" // && ( // ISFILTERED ( TD_EXTERNAL_PACKINFO[PROD_COD] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PROD_CORP] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PROD_DES] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PROD_DES_C] ) // ), // CALCULATE ( // [External.Value.MTH.MID], // TF_EXTERNAL_SALES[PROD_FLAG] IN { 1, 2 }, // TF_EXTERNAL_SALES[DTP_FLAG] = 0 // ), // //若选择地理,且省份维度不是全国 // DS = "Retail(Quarterly)" // && SELECTEDVALUE ( TD_EXTERNAL_GEO_Analysis[PROVINCE_C] ) <> "全国" // && ( // ISFILTERED ( TD_EXTERNAL_GEO_Analysis ) || ISFILTERED ( TD_EXTERNAL_GEO_MID ) // ), // CALCULATE ( // [External.Value.MTH.MID], // TF_EXTERNAL_SALES[GEO_FLAG] = 1, // TF_EXTERNAL_SALES[DTP_FLAG] = 0 // ), CALCULATE ( [External.Value.MTH.MID_THC_2] // , Fact_Sales[DTP_FLAG] = 0 ) ) ``` displayFolder: 01.Sales lineageTag: d35891cf-a852-43f0-8074-09256df20356 measure 'External.Value.MTH.MID_THC_2' = SWITCH ( TRUE (), ISFILTERED ( Dim_Org ) && ISFILTERED ( Dim_GeoGroup ), BLANK (), ISFILTERED( Dim_Org ) && NOT ISFILTERED( Dim_Market) && NOT ISFILTERED(Dim_BrandDefaultMKT),BLANK(), [External.Value.MID_THC_1]) displayFolder: 01.Sales lineageTag: 652e2e91-2140-496c-9723-068c0eea3714 annotation PBI_FormatHint = {"isGeneralNumber":true} measure 'External.CountingUnit.MID_THC1' = ``` VAR MKT = VALUES(Dim_Market[MKT_SOURCE]) VAR AUDIT = CALCULATETABLE(VALUES(Dim_RC[AUDIT_SOURCE]),Dim_RC[MKT_SOURCE] IN MKT) // VAR INST = CALCULATETABLE(VALUES(Dim_IsTarget[INST_SOURCE]),Dim_IsTarget[MKT_SOURCE] IN MKT) VAR DEPT = CALCULATETABLE(VALUES(Dim_CoreDept[DEPT_SOURCE]),Dim_CoreDept[MKT_SOURCE] IN MKT) RETURN SWITCH ( TRUE (), ( ISFILTERED ( Dim_Market ) || ISFILTERED ( Dim_BrandDefaultMKT ) ) && ISFILTERED ( Dim_Org ), CALCULATE( SUMX ( Fact_Sales_THC, Fact_Sales_THC[CONUTING_UNIT] * [External.SelectCountingUnitMKTRatio] * [External.SelectRegionMKTRatio] ), TREATAS(AUDIT,Dim_GeoKey[AUDIT_SOURCE]) ), ( ISFILTERED ( Dim_Market ) || ISFILTERED ( Dim_BrandDefaultMKT ) ) && (--ISFILTERED ( Dim_IsTarget ) || ISFILTERED(Dim_IsTarget_AIASpecial) || ISFILTERED(Dim_HP_Potential_AIA)), CALCULATE( SUMX ( Fact_Sales_THC, Fact_Sales_THC[CONUTING_UNIT] * [External.SelectCountingUnitMKTRatio] ) // , // TREATAS(INST,Dim_Institution[INST_SOURCE]) ), ( ISFILTERED ( Dim_Market ) || ISFILTERED ( Dim_BrandDefaultMKT ) ) && ISFILTERED ( Dim_CoreDept ), CALCULATE( SUMX ( Fact_Sales_THC, Fact_Sales_THC[CONUTING_UNIT] * [External.SelectCountingUnitMKTRatio] ), TREATAS(DEPT,Dim_Dept[DEPT_SOURCE]) ), ( ISFILTERED ( Dim_Market ) || ISFILTERED ( Dim_BrandDefaultMKT ) || ISFILTERED ( Dim_MarketTA ) || ISFILTERED(Dim_Class)), SUMX ( Fact_Sales_THC, Fact_Sales_THC[CONUTING_UNIT] * [External.SelectCountingUnitMKTRatio] ), SUMX ( Fact_Sales_THC, [CONUTING_UNIT] ) ) ``` displayFolder: 01.Sales\币种KPI lineageTag: 422a2622-df76-47e9-b6e9-2ab1fc08cda5 annotation PBI_FormatHint = {"isGeneralNumber":true} measure 'External.CountingUnit.MTH_THC3' = ``` VAR TA = SELECTEDVALUE ( Dim_MarketTA[TA] ) VAR DS = SELECTEDVALUE ( Dim_DataSource[SOURCENAME] ) VAR MKTTA = MAX(Dim_Market[TA]) RETURN SWITCH ( TRUE (), //Retail 有一个TA是单独的，买的DTP数据 DS = "Retail" && ISFILTERED(Dim_MarketTA) && TA = "ONCO", CALCULATE ( [External.CountingUnit.MTH.MID_THC2], Fact_Sales[DTP_FLAG] = 1 ), DS = "Retail" && MKTTA = "ONCO", CALCULATE ( [External.CountingUnit.MTH.MID_THC2], Fact_Sales[DTP_FLAG] = 1 ), // //若选择PACKAGE,不选择地理维度，1表示pack+省份数据；2表示pack+全国的数据 // DS = "Retail(Quarterly)" // && ( // ISFILTERED ( TD_EXTERNAL_PACKINFO[PACK_DES] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PROD_PACK] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PACK_COD] ) // ) // && NOT ISFILTERED ( TD_EXTERNAL_GEO_Analysis ) // && NOT ISFILTERED ( TD_EXTERNAL_GEO_MID ), // CALCULATE ( // [External.CountingUnit.MTH.MID], // TF_EXTERNAL_SALES[PACK_FLAG] IN { 1, 2 }, // TF_EXTERNAL_SALES[DTP_FLAG] = 0 // ), // //若选择PACKAGE,且省份维度是全国 // DS = "Retail(Quarterly)" // && ( // ISFILTERED ( TD_EXTERNAL_PACKINFO[PACK_DES] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PROD_PACK] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PACK_COD] ) // ) // && SELECTEDVALUE ( TD_EXTERNAL_GEO_Analysis[PROVINCE_C] ) = "全国", // CALCULATE ( // [External.CountingUnit.MTH.MID], // TF_EXTERNAL_SALES[PACK_FLAG] IN { 1, 2 }, // TF_EXTERNAL_SALES[DTP_FLAG] = 0 // ), // //若选择PACKAGE,且省份维度不是全国 // DS = "Retail(Quarterly)" // && ( // ISFILTERED ( TD_EXTERNAL_PACKINFO[PACK_DES] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PROD_PACK] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PACK_COD] ) // ) // && SELECTEDVALUE ( TD_EXTERNAL_GEO_Analysis[PROVINCE_C] ) <> "全国", // CALCULATE ( // [External.CountingUnit.MTH.MID], // TF_EXTERNAL_SALES[PACK_FLAG] = 1, // TF_EXTERNAL_SALES[DTP_FLAG] = 0 // ), // //若选择地理,且省份维度不是全国 // DS = "Retail(Quarterly)" // && SELECTEDVALUE ( TD_EXTERNAL_GEO_Analysis[PROVINCE_C] ) <> "全国" // && ( // ISFILTERED ( TD_EXTERNAL_GEO_Analysis ) || ISFILTERED ( TD_EXTERNAL_GEO_MID ) // ), // CALCULATE ( // [External.CountingUnit.MTH.MID], // TF_EXTERNAL_SALES[GEO_FLAG] = 1, // TF_EXTERNAL_SALES[DTP_FLAG] = 0 // ), // //若选择BRAND, // DS = "Retail(Quarterly)" // && ( // ISFILTERED ( TD_EXTERNAL_PACKINFO[PROD_COD] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PROD_CORP] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PROD_DES] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PROD_DES_C] ) // ), // CALCULATE ( // [External.CountingUnit.MTH.MID], // TF_EXTERNAL_SALES[PROD_FLAG] IN { 1, 2 }, // TF_EXTERNAL_SALES[DTP_FLAG] = 0 // ), CALCULATE ( [External.CountingUnit.MTH.MID_THC2], Fact_Sales[DTP_FLAG] = 0 ) ) ``` displayFolder: 01.Sales\币种KPI lineageTag: 0b54a797-2d91-4476-8832-58381a141ee2 measure 'External.CountingUnit.MTH.MID_THC2' = SWITCH ( TRUE (), ISFILTERED ( Dim_Org ) && ISFILTERED ( Dim_GeoGroup ), BLANK (), ISFILTERED( Dim_Org ) && NOT ISFILTERED( Dim_Market) && NOT ISFILTERED(Dim_BrandDefaultMKT),BLANK(), [External.CountingUnit.MID_THC1] ) displayFolder: 01.Sales\币种KPI lineageTag: dcc2d271-f683-4873-b851-b9015b6d1ff2 annotation PBI_FormatHint = {"isGeneralNumber":true} measure 'External.PDOT.MID_THC1' = ``` VAR MKT = VALUES(Dim_Market[MKT_SOURCE]) VAR AUDIT = CALCULATETABLE(VALUES(Dim_RC[AUDIT_SOURCE]),Dim_RC[MKT_SOURCE] IN MKT) // VAR INST = CALCULATETABLE(VALUES(Dim_IsTarget[INST_SOURCE]),Dim_IsTarget[MKT_SOURCE] IN MKT) VAR DEPT = CALCULATETABLE(VALUES(Dim_CoreDept[DEPT_SOURCE]),Dim_CoreDept[MKT_SOURCE] IN MKT) RETURN SWITCH ( TRUE (), ( ISFILTERED ( Dim_Market ) || ISFILTERED ( Dim_BrandDefaultMKT ) ) && ISFILTERED ( Dim_Org ), CALCULATE( SUMX ( Fact_Sales_THC, Fact_Sales_THC[CONUTING_UNIT] * [External.SelectPDOTMKTRatio] * [External.SelectRegionMKTRatio] ), TREATAS(AUDIT,Dim_GeoKey[AUDIT_SOURCE]) ), ( ISFILTERED ( Dim_Market ) || ISFILTERED ( Dim_BrandDefaultMKT ) ) && (--ISFILTERED ( Dim_IsTarget ) || ISFILTERED(Dim_IsTarget_AIASpecial) || ISFILTERED(Dim_HP_Potential_AIA)), CALCULATE( SUMX ( Fact_Sales_THC, Fact_Sales_THC[CONUTING_UNIT] * [External.SelectPDOTMKTRatio] ) // , // TREATAS(INST,Dim_Institution[INST_SOURCE]) ), ( ISFILTERED ( Dim_Market ) || ISFILTERED ( Dim_BrandDefaultMKT ) ) && ISFILTERED ( Dim_CoreDept ), CALCULATE( SUMX ( Fact_Sales_THC, Fact_Sales_THC[CONUTING_UNIT] * [External.SelectPDOTMKTRatio] ), TREATAS(DEPT,Dim_Dept[DEPT_SOURCE]) ), ( ISFILTERED ( Dim_Market ) || ISFILTERED ( Dim_BrandDefaultMKT ) || ISFILTERED ( Dim_MarketTA ) || ISFILTERED(Dim_Class)), SUMX ( Fact_Sales_THC, Fact_Sales_THC[CONUTING_UNIT] * [External.SelectPDOTMKTRatio] ), SUMX ( Fact_Sales_THC, [CONUTING_UNIT] ) ) ``` displayFolder: 01.Sales\币种KPI lineageTag: c8ac5722-7788-442d-8ebd-4ccb02b6ab40 annotation PBI_FormatHint = {"isGeneralNumber":true} measure 'External.PDOT.MTH_THC3' = ``` VAR TA = SELECTEDVALUE ( Dim_MarketTA[TA] ) VAR DS = SELECTEDVALUE ( Dim_DataSource[SOURCENAME] ) VAR MKTTA = MAX(Dim_Market[TA]) RETURN SWITCH ( TRUE (), //Retail 有一个TA是单独的，买的DTP数据 DS = "Retail" && ISFILTERED(Dim_MarketTA) && TA = "ONCO", CALCULATE ( [External.PDOT.MTH.MID_THC2], Fact_Sales[DTP_FLAG] = 1 ), DS = "Retail" && MKTTA = "ONCO", CALCULATE ( [External.PDOT.MTH.MID_THC2], Fact_Sales[DTP_FLAG] = 1 ), // //若选择PACKAGE,不选择地理维度，1表示pack+省份数据；2表示pack+全国的数据 // DS = "Retail(Quarterly)" // && ( // ISFILTERED ( TD_EXTERNAL_PACKINFO[PACK_DES] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PROD_PACK] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PACK_COD] ) // ) // && NOT ISFILTERED ( TD_EXTERNAL_GEO_Analysis ) // && NOT ISFILTERED ( TD_EXTERNAL_GEO_MID ), // CALCULATE ( // [External.PDOT.MTH.MID], // TF_EXTERNAL_SALES[PACK_FLAG] IN { 1, 2 }, // TF_EXTERNAL_SALES[DTP_FLAG] = 0 // ), // //若选择PACKAGE,且省份维度是全国 // DS = "Retail(Quarterly)" // && ( // ISFILTERED ( TD_EXTERNAL_PACKINFO[PACK_DES] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PROD_PACK] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PACK_COD] ) // ) // && SELECTEDVALUE ( TD_EXTERNAL_GEO_Analysis[PROVINCE_C] ) = "全国", // CALCULATE ( // [External.PDOT.MTH.MID], // TF_EXTERNAL_SALES[PACK_FLAG] IN { 1, 2 }, // TF_EXTERNAL_SALES[DTP_FLAG] = 0 // ), // //若选择PACKAGE,且省份维度不是全国 // DS = "Retail(Quarterly)" // && ( // ISFILTERED ( TD_EXTERNAL_PACKINFO[PACK_DES] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PROD_PACK] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PACK_COD] ) // ) // && SELECTEDVALUE ( TD_EXTERNAL_GEO_Analysis[PROVINCE_C] ) <> "全国", // CALCULATE ( // [External.PDOT.MTH.MID], // TF_EXTERNAL_SALES[PACK_FLAG] = 1, // TF_EXTERNAL_SALES[DTP_FLAG] = 0 // ), // //若选择地理,且省份维度不是全国 // DS = "Retail(Quarterly)" // && SELECTEDVALUE ( TD_EXTERNAL_GEO_Analysis[PROVINCE_C] ) <> "全国" // && ( // ISFILTERED ( TD_EXTERNAL_GEO_Analysis ) || ISFILTERED ( TD_EXTERNAL_GEO_MID ) // ), // CALCULATE ( // [External.PDOT.MTH.MID], // TF_EXTERNAL_SALES[GEO_FLAG] = 1, // TF_EXTERNAL_SALES[DTP_FLAG] = 0 // ), // //若选择BRAND, // DS = "Retail(Quarterly)" // && ( // ISFILTERED ( TD_EXTERNAL_PACKINFO[PROD_COD] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PROD_CORP] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PROD_DES] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PROD_DES_C] ) // ), // CALCULATE ( // [External.PDOT.MTH.MID], // TF_EXTERNAL_SALES[PROD_FLAG] IN { 1, 2 }, // TF_EXTERNAL_SALES[DTP_FLAG] = 0 // ), CALCULATE ( [External.PDOT.MTH.MID_THC2], Fact_Sales[DTP_FLAG] = 0 ) ) ``` displayFolder: 01.Sales\币种KPI lineageTag: 4d158aa2-8d85-41be-a846-8610d0a53da1 measure 'External.PDOT.MTH.MID_THC2' = SWITCH ( TRUE (), ISFILTERED ( Dim_Org ) && ISFILTERED ( Dim_GeoGroup ), BLANK (), ISFILTERED( Dim_Org ) && NOT ISFILTERED( Dim_Market) && NOT ISFILTERED(Dim_BrandDefaultMKT),BLANK(), [External.PDOT.MID_THC1] ) displayFolder: 01.Sales\币种KPI lineageTag: 39cc16bf-5775-4790-a6dc-9340e89b974c annotation PBI_FormatHint = {"isGeneralNumber":true} measure 'External.Prescription.MTH _THC1' = ``` VAR MKT = VALUES(Dim_Market[MKT_SOURCE]) VAR AUDIT = CALCULATETABLE(VALUES(Dim_RC[AUDIT_SOURCE]),Dim_RC[MKT_SOURCE] IN MKT) // VAR INST = CALCULATETABLE(VALUES(Dim_IsTarget[INST_SOURCE]),Dim_IsTarget[MKT_SOURCE] IN MKT) VAR DEPT = CALCULATETABLE(VALUES(Dim_CoreDept[DEPT_SOURCE]),Dim_CoreDept[MKT_SOURCE] IN MKT) RETURN SWITCH ( TRUE (), ( ISFILTERED ( Dim_Market ) || ISFILTERED ( Dim_BrandDefaultMKT ) ) && ISFILTERED ( Dim_Org ), CALCULATE( SUMX ( Fact_Sales_THC, Fact_Sales_THC[PRESCRIPTION] ), TREATAS(AUDIT,Dim_GeoKey[AUDIT_SOURCE]) ), ( ISFILTERED ( Dim_Market ) || ISFILTERED ( Dim_BrandDefaultMKT ) ) // && ISFILTERED ( Dim_IsTarget ) , CALCULATE( SUMX ( Fact_Sales_THC, Fact_Sales_THC[PRESCRIPTION] ) // , // TREATAS(INST,Dim_Institution[INST_SOURCE]) ), ( ISFILTERED ( Dim_Market ) || ISFILTERED ( Dim_BrandDefaultMKT ) ) && ISFILTERED ( Dim_CoreDept ), CALCULATE( SUMX ( Fact_Sales_THC, Fact_Sales_THC[PRESCRIPTION] ), TREATAS(DEPT,Dim_Dept[DEPT_SOURCE]) ), SUMX ( Fact_Sales_THC, [PRESCRIPTION] ) ) ``` displayFolder: 01.Sales\币种KPI lineageTag: 4bc0cfc3-2e3a-4a30-9bf6-93135dbe97c5 annotation PBI_FormatHint = {"isGeneralNumber":true} measure 'External.Unit.MID_THC1' = ``` VAR MKT = VALUES(Dim_Market[MKT_SOURCE]) VAR AUDIT = CALCULATETABLE(VALUES(Dim_RC[AUDIT_SOURCE]),Dim_RC[MKT_SOURCE] IN MKT) // VAR INST = CALCULATETABLE(VALUES(Dim_IsTarget[INST_SOURCE]),Dim_IsTarget[MKT_SOURCE] IN MKT) VAR DEPT = CALCULATETABLE(VALUES(Dim_CoreDept[DEPT_SOURCE]),Dim_CoreDept[MKT_SOURCE] IN MKT) RETURN SWITCH ( TRUE (), ( ISFILTERED ( Dim_Market ) || ISFILTERED ( Dim_BrandDefaultMKT ) ) && ISFILTERED ( Dim_Org ), CALCULATE( SUMX ( Fact_Sales_THC, Fact_Sales_THC[SALES_UNIT_CAL] * [External.SelectUnitMKTRatio] * [External.SelectRegionMKTRatio] ), TREATAS(AUDIT,Dim_GeoKey[AUDIT_SOURCE]) ), ( ISFILTERED ( Dim_Market ) || ISFILTERED ( Dim_BrandDefaultMKT ) ) && (--ISFILTERED ( Dim_IsTarget ) || ISFILTERED(Dim_IsTarget_AIASpecial) || ISFILTERED(Dim_HP_Potential_AIA)), CALCULATE( SUMX ( Fact_Sales_THC, Fact_Sales_THC[SALES_UNIT_CAL] * [External.SelectUnitMKTRatio] ) // , // TREATAS(INST,Dim_Institution[INST_SOURCE]) ), ( ISFILTERED ( Dim_Market ) || ISFILTERED ( Dim_BrandDefaultMKT ) ) && ISFILTERED ( Dim_CoreDept ), CALCULATE( SUMX ( Fact_Sales_THC, Fact_Sales_THC[SALES_UNIT_CAL] * [External.SelectUnitMKTRatio] ), TREATAS(DEPT,Dim_Dept[DEPT_SOURCE]) ), ( ISFILTERED ( Dim_Market ) || ISFILTERED ( Dim_BrandDefaultMKT ) || ISFILTERED ( Dim_MarketTA ) || ISFILTERED(Dim_Class)), SUMX ( Fact_Sales_THC, Fact_Sales_THC[SALES_UNIT_CAL] * [External.SelectUnitMKTRatio] ), SUMX ( Fact_Sales_THC, [SALES_UNIT_CAL] ) ) ``` displayFolder: 01.Sales\币种KPI lineageTag: e94fafaa-5919-436d-bcfd-4c422afa2b88 annotation PBI_FormatHint = {"isGeneralNumber":true} measure 'External.Unit.MTH_THC3' = ``` VAR TA = SELECTEDVALUE ( Dim_MarketTA[TA] ) VAR DS = SELECTEDVALUE ( Dim_DataSource[SOURCENAME] ) VAR MKTTA = MAX(Dim_Market[TA]) RETURN SWITCH ( TRUE (), //Retail 有一个TA是单独的，买的DTP数据 DS = "Retail" && ISFILTERED(Dim_MarketTA) && TA = "ONCO", CALCULATE ( [External.Unit.MTH.MID_THC2], Fact_Sales[DTP_FLAG] = 1 ), DS = "Retail" && MKTTA = "ONCO", CALCULATE ( [External.Unit.MTH.MID_THC2], Fact_Sales[DTP_FLAG] = 1 ), // //若选择PACKAGE,不选择地理维度，1表示pack+省份数据；2表示pack+全国的数据 // DS = "Retail(Quarterly)" // && ( // ISFILTERED ( TD_EXTERNAL_PACKINFO[PACK_DES] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PROD_PACK] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PACK_COD] ) // ) // && NOT ISFILTERED ( TD_EXTERNAL_GEO_Analysis ) // && NOT ISFILTERED ( TD_EXTERNAL_GEO_MID ), // CALCULATE ( // [External.Unit.MTH.MID], // TF_EXTERNAL_SALES[PACK_FLAG] IN { 1, 2 }, // TF_EXTERNAL_SALES[DTP_FLAG] = 0 // ), // //若选择PACKAGE,且省份维度是全国 // DS = "Retail(Quarterly)" // && ( // ISFILTERED ( TD_EXTERNAL_PACKINFO[PACK_DES] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PROD_PACK] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PACK_COD] ) // ) // && SELECTEDVALUE ( TD_EXTERNAL_GEO_Analysis[PROVINCE_C] ) = "全国", // CALCULATE ( // [External.Unit.MTH.MID], // TF_EXTERNAL_SALES[PACK_FLAG] IN { 1, 2 }, // TF_EXTERNAL_SALES[DTP_FLAG] = 0 // ), // //若选择PACKAGE,且省份维度不是全国 // DS = "Retail(Quarterly)" // && ( // ISFILTERED ( TD_EXTERNAL_PACKINFO[PACK_DES] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PROD_PACK] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PACK_COD] ) // ) // && SELECTEDVALUE ( TD_EXTERNAL_GEO_Analysis[PROVINCE_C] ) <> "全国", // CALCULATE ( // [External.Unit.MTH.MID], // TF_EXTERNAL_SALES[PACK_FLAG] = 1, // TF_EXTERNAL_SALES[DTP_FLAG] = 0 // ), // //若选择地理,且省份维度不是全国 // DS = "Retail(Quarterly)" // && SELECTEDVALUE ( TD_EXTERNAL_GEO_Analysis[PROVINCE_C] ) <> "全国" // && ( // ISFILTERED ( TD_EXTERNAL_GEO_Analysis ) || ISFILTERED ( TD_EXTERNAL_GEO_MID ) // ), // CALCULATE ( // [External.Unit.MTH.MID], // TF_EXTERNAL_SALES[GEO_FLAG] = 1, // TF_EXTERNAL_SALES[DTP_FLAG] = 0 // ), // //若选择BRAND, // DS = "Retail(Quarterly)" // && ( // ISFILTERED ( TD_EXTERNAL_PACKINFO[PROD_COD] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PROD_CORP] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PROD_DES] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PROD_DES_C] ) // ), // CALCULATE ( // [External.Unit.MTH.MID], // TF_EXTERNAL_SALES[PROD_FLAG] IN { 1, 2 }, // TF_EXTERNAL_SALES[DTP_FLAG] = 0 // ), CALCULATE ( [External.Unit.MTH.MID_THC2], Fact_Sales[DTP_FLAG] = 0 ) ) ``` displayFolder: 01.Sales\币种KPI lineageTag: 0dcef6fa-140a-41bc-93e0-58cbbd87f939 measure 'External.Unit.MTH.MID_THC2' = SWITCH ( TRUE (), ISFILTERED ( Dim_Org ) && ISFILTERED ( Dim_GeoGroup ), BLANK (), ISFILTERED( Dim_Org ) && NOT ISFILTERED( Dim_Market) && NOT ISFILTERED(Dim_BrandDefaultMKT),BLANK(), [External.Unit.MID_THC1] ) displayFolder: 01.Sales\币种KPI lineageTag: 0de7f412-bc88-4984-9f65-bbbc0bc785a8 annotation PBI_FormatHint = {"isGeneralNumber":true} measure 'External.Sales.LY.KPI_THC6' = [External.Sales.LY.MTH.MID_THC5] formatString: #,0.00 displayFolder: 02.Sales LY lineageTag: 2d6bdf7a-1280-4504-abc1-21c1d8647f69 measure 'External.Sales.LY.MTH_THC4' = ``` VAR SalesKPI = SWITCH([External.SelectUnit], "RMB",[External.Value.LY.MTH_THC3], "K RMB",DIVIDE([External.Value.LY.MTH_THC3],1000), "M RMB",DIVIDE([External.Value.LY.MTH_THC3],1000000), "USD",DIVIDE([External.Value.LY.MTH_THC3],[ExchangeRate]), "K USD",DIVIDE(DIVIDE([External.Value.LY.MTH_THC3],[ExchangeRate]),1000), "M USD",DIVIDE(DIVIDE([External.Value.LY.MTH_THC3],[ExchangeRate]),1000000), "Unit",[External.Unit.LY.MTH_THC3], "K Unit",DIVIDE([External.Unit.LY.MTH_THC3],1000), "M Unit",DIVIDE([External.Unit.LY.MTH_THC3],1000000), "CU",[External.CountingUnit.LY.MTH_THC3], "K CU",DIVIDE([External.CountingUnit.LY.MTH_THC3],1000), "M CU",DIVIDE([External.CountingUnit.LY.MTH_THC3],1000000), "PDOT",[External.PDOT.LY.MTH_THC3], "K PDOT",DIVIDE([External.PDOT.LY.MTH_THC3],1000), "M PDOT",DIVIDE([External.PDOT.LY.MTH_THC3],1000000), "张数", [External.Prescription.LY.MTH_THC1] // "K Rx", DIVIDE ( [External.Prescription.LY.MTH], 1000 ), // "M Rx", DIVIDE ( [External.Prescription.LY.MTH], 1000000 ) ) VAR Channel = ALLSELECTED (Dim_DataSource[SOURCENAME]) VAR IsCounty = "County" IN Channel VAR IsCHC = "CHC" IN Channel VAR PROV = SELECTEDVALUE(Dim_GeoGroup[PROVINCE_GROUP]) RETURN SWITCH ( TRUE (), IsCounty = FALSE () && Prov IN {"BBU COUNTY","OBU COUNTY","BBU_BU_COUNTY","OBU_BU_COUNTY","OBU East5","OBU NonEast5","OBU EAST5","OBU NONEAST5"}, BLANK (), IsCHC = FALSE () && Prov = "CHC13城市", BLANK (), SalesKPI ) ``` displayFolder: 02.Sales LY lineageTag: 53fb14dc-8d70-42f5-930f-8d69b5ce6d99 measure 'External.Sales.LY.MTH.MID_THC5' = ``` VAR DS = SELECTEDVALUE ( Dim_DataSource[SOURCENAME] ) VAR DS1 = SELECTEDVALUE(Dim_DataSource[DATA_SOURCE]) VAR MKT_FLAG = SWITCH(TRUE(), ISFILTERED(Dim_Market), MAX(Dim_Market[MKT_FLAG]), ISFILTERED(Dim_BrandDefaultMKT), CALCULATE(MAX(Dim_BrandDefaultMKT[MKT_FLAG]),Dim_BrandDefaultMKT[DATA_SOURCE] = DS1), 0) VAR PROD_FLAG = SWITCH(TRUE(), ISFILTERED(Dim_Market), MAX(Dim_Market[PRD_FLAG]), ISFILTERED(Dim_BrandDefaultMKT), CALCULATE(MAX(Dim_BrandDefaultMKT[PRD_FLAG]),Dim_BrandDefaultMKT[DATA_SOURCE] = DS1), 0) RETURN SWITCH ( TRUE (), // ISFILTERED(Dim_IsTarget) && NOT ISFILTERED(Dim_Market) && NOT ISFILTERED(Dim_BrandDefaultMKT),BLANK(), ISFILTERED(Dim_CoreDept) && NOT ISFILTERED(Dim_Market) && NOT ISFILTERED(Dim_BrandDefaultMKT),BLANK(), //若选择地理,且省份维度不是全国 DS IN {"Retail","EC"} && MKT_FLAG = 1 && NOT ISFILTERED ( Dim_PackInfo[PROD_COD] ) && NOT ISFILTERED ( Dim_PackInfo[PROD_DES_C] ) && NOT ISFILTERED ( Dim_PackInfo[PROD_DES] ) && NOT ISFILTERED ( Dim_PackInfo[PROD_CORP] ) && NOT ISFILTERED ( Dim_PackInfo[PACK_COD] ) && NOT ISFILTERED ( Dim_PackInfo[PROD_CORP_C] ) && NOT ISFILTERED ( Dim_PackInfo[PROD_PACK_CORP] ) && NOT ISFILTERED ( Dim_PackInfo[PROD_PACK] ) && NOT ISFILTERED ( Dim_PackInfo[PACK_DES] ) && NOT ISFILTERED ( Dim_PackInfo[PACK_LCH] ) && ISFILTERED(Dim_Geo) && ( ISFILTERED ( Dim_PackInfo[MOLECULE_CODE_DESC] ) || ISFILTERED ( Dim_PackInfo[MOLECULE_DES_C] ) ||ISFILTERED ( Dim_PackInfo[CMPS_COD] ) || ISFILTERED ( Dim_PackInfo[CMPS_DES] ) || ISFILTERED ( Dim_PackInfo[CMPS_DES_C] )), [External.Sales.LY.MTH_THC4], //除开特定市场外，其余的retail，针对于class，选择地理或org维度，值为空 DS = "Retail" && (ISFILTERED(Dim_Geo) || ISFILTERED(Dim_Org)) && ISFILTERED(Dim_Class) && (ISFILTERED(Dim_Market) || ISFILTERED(Dim_BrandDefaultMKT)) && MKT_FLAG = 0,BLANK(), //选择市场，只出现品牌报告里面的有市场； DS = "Retail" && (ISFILTERED(Dim_Geo) || ISFILTERED(Dim_Org)) && (ISFILTERED(Dim_Market) || ISFILTERED(Dim_BrandDefaultMKT)) && PROD_FLAG = 0,BLANK(), --若不选pack，用于计算class相关KPI DS IN {"Retail","EC"} && (ISFILTERED ( Dim_Geo ) || ISFILTERED ( Dim_Org )) && NOT ISFILTERED(Dim_PackInfo), [External.Sales.LY.MTH_THC4], //若选择Molecule,没选择地理 DS IN {"Retail","EC"} && NOT ISFILTERED(Dim_Geo) && NOT ISFILTERED(Dim_Org) && NOT ISFILTERED ( Dim_PackInfo[PROD_COD] ) && NOT ISFILTERED ( Dim_PackInfo[PROD_DES_C] ) && NOT ISFILTERED ( Dim_PackInfo[PROD_DES] ) && NOT ISFILTERED ( Dim_PackInfo[PROD_CORP] ) && NOT ISFILTERED ( Dim_PackInfo[PACK_COD] ) && NOT ISFILTERED ( Dim_PackInfo[PROD_CORP_C] ) && NOT ISFILTERED ( Dim_PackInfo[PROD_PACK_CORP] ) && NOT ISFILTERED ( Dim_PackInfo[PROD_PACK] ) && NOT ISFILTERED ( Dim_PackInfo[PACK_DES] ) && NOT ISFILTERED ( Dim_PackInfo[PACK_LCH] ) && ( ISFILTERED ( Dim_PackInfo[MOLECULE_CODE_DESC] ) || ISFILTERED ( Dim_PackInfo[MOLECULE_DES_C] ) ||ISFILTERED ( Dim_PackInfo[CMPS_COD] ) || ISFILTERED ( Dim_PackInfo[CMPS_DES] ) || ISFILTERED ( Dim_PackInfo[CMPS_DES_C] )), [External.Sales.LY.MTH_THC4], //若选择Molecule,选择地理 DS IN {"Retail","EC"} && NOT ISFILTERED ( Dim_PackInfo[PROD_COD] ) && NOT ISFILTERED ( Dim_PackInfo[PROD_DES_C] ) && NOT ISFILTERED ( Dim_PackInfo[PROD_DES] ) && NOT ISFILTERED ( Dim_PackInfo[PROD_CORP] ) && NOT ISFILTERED ( Dim_PackInfo[PACK_COD] ) && NOT ISFILTERED ( Dim_PackInfo[PROD_CORP_C] ) && NOT ISFILTERED ( Dim_PackInfo[PROD_PACK_CORP] ) && NOT ISFILTERED ( Dim_PackInfo[PROD_PACK] ) && NOT ISFILTERED ( Dim_PackInfo[PACK_DES] ) && NOT ISFILTERED ( Dim_PackInfo[PACK_LCH] ) && ( ISFILTERED ( Dim_PackInfo[MOLECULE_CODE_DESC] ) || ISFILTERED ( Dim_PackInfo[MOLECULE_DES_C] ) ||ISFILTERED ( Dim_PackInfo[CMPS_COD] ) || ISFILTERED ( Dim_PackInfo[CMPS_DES] ) || ISFILTERED ( Dim_PackInfo[CMPS_DES_C] )), CALCULATE ( [External.Sales.LY.MTH_THC4], Fact_Sales[CMPS_FLAG] = 1 ), //若选择BRAND,没有选择地理 DS IN {"Retail","EC"} && NOT ISFILTERED ( Dim_Geo ) && NOT ISFILTERED ( Dim_Org ) && NOT ISFILTERED ( Dim_PackInfo[PACK_COD] ) && NOT ISFILTERED ( Dim_PackInfo[PROD_PACK_CORP] ) && NOT ISFILTERED ( Dim_PackInfo[PACK_DES] ) && NOT ISFILTERED ( Dim_PackInfo[PACK_LCH] ) && (ISFILTERED ( Dim_PackInfo[PROD_COD] ) || ISFILTERED ( Dim_PackInfo[PROD_CORP_C] ) || ISFILTERED ( Dim_PackInfo[PROD_DES] ) || ISFILTERED ( Dim_PackInfo[PROD_DES_C] )), CALCULATE ( [External.Sales.LY.MTH_THC4], Fact_Sales[PROD_FLAG] IN {1, 2} ), //若选择BRAND,选择地理 DS IN {"Retail","EC"} && NOT ISFILTERED ( Dim_PackInfo[PACK_COD] ) && NOT ISFILTERED ( Dim_PackInfo[PROD_PACK_CORP] ) && NOT ISFILTERED ( Dim_PackInfo[PACK_DES] ) && NOT ISFILTERED ( Dim_PackInfo[PACK_LCH] ) && (ISFILTERED ( Dim_PackInfo[PROD_COD] ) || ISFILTERED ( Dim_PackInfo[PROD_CORP_C] ) || ISFILTERED ( Dim_PackInfo[PROD_DES] ) || ISFILTERED ( Dim_PackInfo[PROD_DES_C] )), CALCULATE ( [External.Sales.LY.MTH_THC4], Fact_Sales[GEO_FLAG] = 1 ), //若选择PACKAGE,不选择地理维度，1表示pack+省份数据；2表示pack+全国的数据 DS IN {"Retail","EC"} && NOT ISFILTERED ( Dim_Org ) && NOT ISFILTERED ( Dim_Geo ) && (ISFILTERED ( Dim_PackInfo[PACK_DES] ) || ISFILTERED ( Dim_PackInfo[PACK_LCH] ) || ISFILTERED ( Dim_PackInfo[PACK_COD] ) || ISFILTERED ( Dim_PackInfo[PROD_PACK_CORP] )), CALCULATE ( [External.Sales.LY.MTH_THC4], Fact_Sales[PACK_FLAG] IN { 1, 2 } ), //若选择PACKAGE,且省份维度不是全国 DS IN {"Retail","EC"} && (ISFILTERED ( Dim_PackInfo[PACK_DES] ) || ISFILTERED ( Dim_PackInfo[PACK_LCH] ) || ISFILTERED ( Dim_PackInfo[PACK_COD] ) || ISFILTERED ( Dim_PackInfo[PROD_PACK_CORP] )), CALCULATE ( [External.Sales.LY.MTH_THC4], Fact_Sales[PACK_FLAG] = 1 ), [External.Sales.LY.MTH_THC4] ) ``` displayFolder: 02.Sales LY lineageTag: 54ec37ca-d8a2-4d09-b319-582b46346f39 annotation PBI_FormatHint = {"isGeneralNumber":true} measure 'External.Value.LY.MID_THC1' = ``` VAR MKT = VALUES(Dim_Market[MKT_SOURCE]) VAR AUDIT = CALCULATETABLE(VALUES(Dim_RC[AUDIT_SOURCE]),Dim_RC[MKT_SOURCE] IN MKT) // VAR INST = CALCULATETABLE(VALUES(Dim_IsTarget[INST_SOURCE]),Dim_IsTarget[MKT_SOURCE] IN MKT) VAR DEPT = CALCULATETABLE(VALUES(Dim_CoreDept[DEPT_SOURCE]),Dim_CoreDept[MKT_SOURCE] IN MKT) RETURN SWITCH ( TRUE (), ( ISFILTERED ( Dim_Market ) || ISFILTERED ( Dim_BrandDefaultMKT ) ) && ISFILTERED ( Dim_Org ), CALCULATE( SUMX ( Fact_Sales_THC, Fact_Sales_THC[SALES_VALUE_CAL_LY] * [External.SelectValueMKTRatio] * [External.SelectRegionMKTRatio] ), TREATAS(AUDIT,Dim_GeoKey[AUDIT_SOURCE]) ), ( ISFILTERED ( Dim_Market ) || ISFILTERED ( Dim_BrandDefaultMKT ) ) && (--ISFILTERED ( Dim_IsTarget ) || ISFILTERED(Dim_IsTarget_AIASpecial)|| ISFILTERED(Dim_HP_Potential_AIA)), CALCULATE( SUMX ( Fact_Sales_THC, Fact_Sales_THC[SALES_VALUE_CAL_LY] * [External.SelectValueMKTRatio] ) // , // TREATAS(INST,Dim_Institution[INST_SOURCE]) ), ( ISFILTERED ( Dim_Market ) || ISFILTERED ( Dim_BrandDefaultMKT ) ) && ISFILTERED ( Dim_CoreDept ), CALCULATE( SUMX ( Fact_Sales_THC, Fact_Sales_THC[SALES_VALUE_CAL_LY] * [External.SelectValueMKTRatio] ), TREATAS(DEPT,Dim_Dept[DEPT_SOURCE]) ), ( ISFILTERED ( Dim_Market ) || ISFILTERED ( Dim_BrandDefaultMKT ) || ISFILTERED ( Dim_MarketTA ) || ISFILTERED(Dim_Class)), SUMX ( Fact_Sales_THC, Fact_Sales_THC[SALES_VALUE_CAL_LY] * [External.SelectValueMKTRatio] ), SUMX ( Fact_Sales_THC, [SALES_VALUE_CAL_LY] ) ) ``` displayFolder: 02.Sales LY lineageTag: 4e04d568-05a6-45f4-b516-18dbf1aaded2 annotation PBI_FormatHint = {"isGeneralNumber":true} measure 'External.Value.LY.MTH_THC3' = ``` VAR TA = SELECTEDVALUE ( Dim_MarketTA[TA] ) VAR DS = SELECTEDVALUE ( Dim_DataSource[SOURCENAME] ) VAR MKTTA = MAX(Dim_Market[TA]) RETURN SWITCH ( TRUE (), //Retail 有一个TA是单独的，买的DTP数据 DS = "Retail" && ISFILTERED(Dim_MarketTA) && TA = "ONCO", CALCULATE ( [External.Value.LY.MTH.MID_THC2], Fact_Sales[DTP_FLAG] = 1 ), DS = "Retail" && MKTTA = "ONCO", CALCULATE ( [External.Value.LY.MTH.MID_THC2], Fact_Sales[DTP_FLAG] = 1 ), // //若选择PACKAGE,不选择地理维度，1表示pack+省份数据；2表示pack+全国的数据 // DS = "Retail(Quarterly)" // && ( // ISFILTERED ( TD_EXTERNAL_PACKINFO[PACK_DES] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PROD_PACK] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PACK_COD] ) // ) // && NOT ISFILTERED ( TD_EXTERNAL_GEO_Analysis ) // && NOT ISFILTERED ( TD_EXTERNAL_GEO_MID ), // CALCULATE ( // [External.Value.LY.MTH.MID], // TF_EXTERNAL_SALES[PACK_FLAG] IN { 1, 2 }, // TF_EXTERNAL_SALES[DTP_FLAG] = 0 // ), // //若选择PACKAGE,且省份维度是全国 // DS = "Retail(Quarterly)" // && ( // ISFILTERED ( TD_EXTERNAL_PACKINFO[PACK_DES] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PROD_PACK] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PACK_COD] ) // ) // && SELECTEDVALUE ( TD_EXTERNAL_GEO_Analysis[PROVINCE_C] ) = "全国", // CALCULATE ( // [External.Value.LY.MTH.MID], // TF_EXTERNAL_SALES[PACK_FLAG] IN { 1, 2 }, // TF_EXTERNAL_SALES[DTP_FLAG] = 0 // ), // //若选择PACKAGE,且省份维度不是全国 // DS = "Retail(Quarterly)" // && ( // ISFILTERED ( TD_EXTERNAL_PACKINFO[PACK_DES] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PROD_PACK] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PACK_COD] ) // ) // && SELECTEDVALUE ( TD_EXTERNAL_GEO_Analysis[PROVINCE_C] ) <> "全国", // CALCULATE ( // [External.Value.LY.MTH.MID], // TF_EXTERNAL_SALES[PACK_FLAG] = 1, // TF_EXTERNAL_SALES[DTP_FLAG] = 0 // ), // //若选择地理,且省份维度不是全国 // DS = "Retail(Quarterly)" // && SELECTEDVALUE ( TD_EXTERNAL_GEO_Analysis[PROVINCE_C] ) <> "全国" // && ( // ISFILTERED ( TD_EXTERNAL_GEO_Analysis ) || ISFILTERED ( TD_EXTERNAL_GEO_MID ) // ), // CALCULATE ( // [External.Value.LY.MTH.MID], // TF_EXTERNAL_SALES[GEO_FLAG] = 1, // TF_EXTERNAL_SALES[DTP_FLAG] = 0 // ), // //若选择BRAND, // DS = "Retail(Quarterly)" // && ( // ISFILTERED ( TD_EXTERNAL_PACKINFO[PROD_COD] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PROD_CORP] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PROD_DES] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PROD_DES_C] ) // ), // CALCULATE ( // [External.Value.LY.MTH.MID], // TF_EXTERNAL_SALES[PROD_FLAG] IN { 1, 2 }, // TF_EXTERNAL_SALES[DTP_FLAG] = 0 // ), CALCULATE ( [External.Value.LY.MTH.MID_THC2], Fact_Sales[DTP_FLAG] = 0 ) ) ``` displayFolder: 02.Sales LY lineageTag: 2008e601-46d9-4ebe-b048-85f2fa649df0 measure 'External.Value.LY.MTH.MID_THC2' = SWITCH ( TRUE (), ISFILTERED ( Dim_Org ) && ISFILTERED ( Dim_GeoGroup ), BLANK (), ISFILTERED( Dim_Org ) && NOT ISFILTERED( Dim_Market) && NOT ISFILTERED(Dim_BrandDefaultMKT),BLANK(), [External.Value.LY.MID_THC1] ) displayFolder: 02.Sales LY lineageTag: 57967969-44bd-4b75-a246-200658b550b0 annotation PBI_FormatHint = {"isGeneralNumber":true} measure 'External.CountingUnit.LY.MID_THC1' = ``` VAR MKT = VALUES(Dim_Market[MKT_SOURCE]) VAR AUDIT = CALCULATETABLE(VALUES(Dim_RC[AUDIT_SOURCE]),Dim_RC[MKT_SOURCE] IN MKT) // VAR INST = CALCULATETABLE(VALUES(Dim_IsTarget[INST_SOURCE]),Dim_IsTarget[MKT_SOURCE] IN MKT) VAR DEPT = CALCULATETABLE(VALUES(Dim_CoreDept[DEPT_SOURCE]),Dim_CoreDept[MKT_SOURCE] IN MKT) RETURN SWITCH ( TRUE (), ( ISFILTERED ( Dim_Market ) || ISFILTERED ( Dim_BrandDefaultMKT ) ) && ISFILTERED ( Dim_Org ), CALCULATE( SUMX ( Fact_Sales_THC, Fact_Sales_THC[CONUTING_UNIT_LY] * [External.SelectCountingUnitMKTRatio] * [External.SelectRegionMKTRatio] ), TREATAS(AUDIT,Dim_GeoKey[AUDIT_SOURCE]) ), ( ISFILTERED ( Dim_Market ) || ISFILTERED ( Dim_BrandDefaultMKT ) ) && (--ISFILTERED ( Dim_IsTarget ) || ISFILTERED(Dim_IsTarget_AIASpecial)), CALCULATE( SUMX ( Fact_Sales_THC, Fact_Sales_THC[CONUTING_UNIT_LY] * [External.SelectCountingUnitMKTRatio] ) // , // TREATAS(INST,Dim_Institution[INST_SOURCE]) ), ( ISFILTERED ( Dim_Market ) || ISFILTERED ( Dim_BrandDefaultMKT ) ) && ISFILTERED ( Dim_CoreDept ), CALCULATE( SUMX ( Fact_Sales_THC, Fact_Sales_THC[CONUTING_UNIT_LY] * [External.SelectCountingUnitMKTRatio] ), TREATAS(DEPT,Dim_Dept[DEPT_SOURCE]) ), ( ISFILTERED ( Dim_Market ) || ISFILTERED ( Dim_BrandDefaultMKT ) || ISFILTERED ( Dim_MarketTA ) || ISFILTERED(Dim_Class)), SUMX ( Fact_Sales_THC, Fact_Sales_THC[CONUTING_UNIT_LY] * [External.SelectCountingUnitMKTRatio] ), SUMX ( Fact_Sales_THC, [CONUTING_UNIT_LY] ) ) ``` displayFolder: 02.Sales LY\币种KPI LY lineageTag: 6a5f19a0-3217-4d40-8284-39db1141c143 annotation PBI_FormatHint = {"isGeneralNumber":true} measure 'External.CountingUnit.LY.MTH_THC3' = ``` VAR TA = SELECTEDVALUE ( Dim_MarketTA[TA] ) VAR DS = SELECTEDVALUE ( Dim_DataSource[SOURCENAME] ) VAR MKTTA = MAX(Dim_Market[TA]) RETURN SWITCH ( TRUE (), //Retail 有一个TA是单独的，买的DTP数据 DS = "Retail" && ISFILTERED(Dim_MarketTA) && TA = "ONCO", CALCULATE ( [External.CountingUnit.LY.MTH.MID_THC2], Fact_Sales[DTP_FLAG] = 1 ), DS = "Retail" && MKTTA = "ONCO", CALCULATE ( [External.CountingUnit.LY.MTH.MID_THC2], Fact_Sales[DTP_FLAG] = 1 ), // //若选择PACKAGE,不选择地理维度，1表示pack+省份数据；2表示pack+全国的数据 // DS = "Retail(Quarterly)" // && ( // ISFILTERED ( TD_EXTERNAL_PACKINFO[PACK_DES] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PROD_PACK] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PACK_COD] ) // ) // && NOT ISFILTERED ( TD_EXTERNAL_GEO_Analysis ) // && NOT ISFILTERED ( TD_EXTERNAL_GEO_MID ), // CALCULATE ( // [External.CountingUnit.LY.MTH.MID], // TF_EXTERNAL_SALES[PACK_FLAG] IN { 1, 2 }, // TF_EXTERNAL_SALES[DTP_FLAG] = 0 // ), // //若选择PACKAGE,且省份维度是全国 // DS = "Retail(Quarterly)" // && ( // ISFILTERED ( TD_EXTERNAL_PACKINFO[PACK_DES] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PROD_PACK] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PACK_COD] ) // ) // && SELECTEDVALUE ( TD_EXTERNAL_GEO_Analysis[PROVINCE_C] ) = "全国", // CALCULATE ( // [External.CountingUnit.LY.MTH.MID], // TF_EXTERNAL_SALES[PACK_FLAG] IN { 1, 2 }, // TF_EXTERNAL_SALES[DTP_FLAG] = 0 // ), // //若选择PACKAGE,且省份维度不是全国 // DS = "Retail(Quarterly)" // && ( // ISFILTERED ( TD_EXTERNAL_PACKINFO[PACK_DES] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PROD_PACK] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PACK_COD] ) // ) // && SELECTEDVALUE ( TD_EXTERNAL_GEO_Analysis[PROVINCE_C] ) <> "全国", // CALCULATE ( // [External.CountingUnit.LY.MTH.MID], // TF_EXTERNAL_SALES[PACK_FLAG] = 1, // TF_EXTERNAL_SALES[DTP_FLAG] = 0 // ), // //若选择地理,且省份维度不是全国 // DS = "Retail(Quarterly)" // && SELECTEDVALUE ( TD_EXTERNAL_GEO_Analysis[PROVINCE_C] ) <> "全国" // && ( // ISFILTERED ( TD_EXTERNAL_GEO_Analysis ) || ISFILTERED ( TD_EXTERNAL_GEO_MID ) // ), // CALCULATE ( // [External.CountingUnit.LY.MTH.MID], // TF_EXTERNAL_SALES[GEO_FLAG] = 1, // TF_EXTERNAL_SALES[DTP_FLAG] = 0 // ), // //若选择BRAND, // DS = "Retail(Quarterly)" // && ( // ISFILTERED ( TD_EXTERNAL_PACKINFO[PROD_COD] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PROD_CORP] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PROD_DES] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PROD_DES_C] ) // ), // CALCULATE ( // [External.CountingUnit.LY.MTH.MID], // TF_EXTERNAL_SALES[PROD_FLAG] IN { 1, 2 }, // TF_EXTERNAL_SALES[DTP_FLAG] = 0 // ), CALCULATE ( [External.CountingUnit.LY.MTH.MID_THC2], Fact_Sales[DTP_FLAG] = 0 ) ) ``` displayFolder: 02.Sales LY\币种KPI LY lineageTag: fa466373-e4d6-4567-9496-f40f4b51d2b7 measure 'External.CountingUnit.LY.MTH.MID_THC2' = SWITCH ( TRUE (), ISFILTERED ( Dim_Org ) && ISFILTERED ( Dim_GeoGroup ), BLANK (), ISFILTERED( Dim_Org ) && NOT ISFILTERED( Dim_Market) && NOT ISFILTERED(Dim_BrandDefaultMKT),BLANK(), [External.CountingUnit.LY.MID_THC1] ) displayFolder: 02.Sales LY\币种KPI LY lineageTag: b22c33e0-0912-4aa5-829b-4b3366869eea annotation PBI_FormatHint = {"isGeneralNumber":true} measure 'External.PDOT.LY.MID_THC1' = ``` VAR MKT = VALUES(Dim_Market[MKT_SOURCE]) VAR AUDIT = CALCULATETABLE(VALUES(Dim_RC[AUDIT_SOURCE]),Dim_RC[MKT_SOURCE] IN MKT) // VAR INST = CALCULATETABLE(VALUES(Dim_IsTarget[INST_SOURCE]),Dim_IsTarget[MKT_SOURCE] IN MKT) VAR DEPT = CALCULATETABLE(VALUES(Dim_CoreDept[DEPT_SOURCE]),Dim_CoreDept[MKT_SOURCE] IN MKT) RETURN SWITCH ( TRUE (), ( ISFILTERED ( Dim_Market ) || ISFILTERED ( Dim_BrandDefaultMKT ) ) && ISFILTERED ( Dim_Org ), CALCULATE( SUMX ( Fact_Sales_THC, Fact_Sales_THC[CONUTING_UNIT_LY] * [External.SelectPDOTMKTRatio] * [External.SelectRegionMKTRatio] ), TREATAS(AUDIT,Dim_GeoKey[AUDIT_SOURCE]) ), ( ISFILTERED ( Dim_Market ) || ISFILTERED ( Dim_BrandDefaultMKT ) ) && (--ISFILTERED ( Dim_IsTarget ) || ISFILTERED(Dim_IsTarget_AIASpecial) || ISFILTERED(Dim_HP_Potential_AIA)), CALCULATE( SUMX ( Fact_Sales_THC, Fact_Sales_THC[CONUTING_UNIT_LY] * [External.SelectPDOTMKTRatio] ) // , // TREATAS(INST,Dim_Institution[INST_SOURCE]) ), ( ISFILTERED ( Dim_Market ) || ISFILTERED ( Dim_BrandDefaultMKT ) ) && ISFILTERED ( Dim_CoreDept ), CALCULATE( SUMX ( Fact_Sales_THC, Fact_Sales_THC[CONUTING_UNIT_LY] * [External.SelectPDOTMKTRatio] ), TREATAS(DEPT,Dim_Dept[DEPT_SOURCE]) ), ( ISFILTERED ( Dim_Market ) || ISFILTERED ( Dim_BrandDefaultMKT ) || ISFILTERED ( Dim_MarketTA ) || ISFILTERED(Dim_Class)), SUMX ( Fact_Sales_THC, Fact_Sales_THC[CONUTING_UNIT_LY] * [External.SelectPDOTMKTRatio] ), SUMX ( Fact_Sales_THC, [CONUTING_UNIT_LY] ) ) ``` displayFolder: 02.Sales LY\币种KPI LY lineageTag: c3f6cafb-605d-4ecd-9d85-8cfca96b6cc5 annotation PBI_FormatHint = {"isGeneralNumber":true} measure 'External.PDOT.LY.MTH_THC3' = ``` VAR TA = SELECTEDVALUE ( Dim_MarketTA[TA] ) VAR DS = SELECTEDVALUE ( Dim_DataSource[SOURCENAME] ) VAR MKTTA = MAX(Dim_Market[TA]) RETURN SWITCH ( TRUE (), //Retail 有一个TA是单独的，买的DTP数据 DS = "Retail" && ISFILTERED(Dim_MarketTA) && TA = "ONCO", CALCULATE ( [External.PDOT.LY.MTH.MID_THC2], Fact_Sales[DTP_FLAG] = 1 ), DS = "Retail" && MKTTA = "ONCO", CALCULATE ( [External.PDOT.LY.MTH.MID_THC2], Fact_Sales[DTP_FLAG] = 1 ), // //若选择PACKAGE,不选择地理维度，1表示pack+省份数据；2表示pack+全国的数据 // DS = "Retail(Quarterly)" // && ( // ISFILTERED ( TD_EXTERNAL_PACKINFO[PACK_DES] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PROD_PACK] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PACK_COD] ) // ) // && NOT ISFILTERED ( TD_EXTERNAL_GEO_Analysis ) // && NOT ISFILTERED ( TD_EXTERNAL_GEO_MID ), // CALCULATE ( // [External.PDOT.LY.MTH.MID], // TF_EXTERNAL_SALES[PACK_FLAG] IN { 1, 2 }, // TF_EXTERNAL_SALES[DTP_FLAG] = 0 // ), // //若选择PACKAGE,且省份维度是全国 // DS = "Retail(Quarterly)" // && ( // ISFILTERED ( TD_EXTERNAL_PACKINFO[PACK_DES] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PROD_PACK] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PACK_COD] ) // ) // && SELECTEDVALUE ( TD_EXTERNAL_GEO_Analysis[PROVINCE_C] ) = "全国", // CALCULATE ( // [External.PDOT.LY.MTH.MID], // TF_EXTERNAL_SALES[PACK_FLAG] IN { 1, 2 }, // TF_EXTERNAL_SALES[DTP_FLAG] = 0 // ), // //若选择PACKAGE,且省份维度不是全国 // DS = "Retail(Quarterly)" // && ( // ISFILTERED ( TD_EXTERNAL_PACKINFO[PACK_DES] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PROD_PACK] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PACK_COD] ) // ) // && SELECTEDVALUE ( TD_EXTERNAL_GEO_Analysis[PROVINCE_C] ) <> "全国", // CALCULATE ( // [External.PDOT.LY.MTH.MID], // TF_EXTERNAL_SALES[PACK_FLAG] = 1, // TF_EXTERNAL_SALES[DTP_FLAG] = 0 // ), // //若选择地理,且省份维度不是全国 // DS = "Retail(Quarterly)" // && SELECTEDVALUE ( TD_EXTERNAL_GEO_Analysis[PROVINCE_C] ) <> "全国" // && ( // ISFILTERED ( TD_EXTERNAL_GEO_Analysis ) || ISFILTERED ( TD_EXTERNAL_GEO_MID ) // ), // CALCULATE ( // [External.PDOT.LY.MTH.MID], // TF_EXTERNAL_SALES[GEO_FLAG] = 1, // TF_EXTERNAL_SALES[DTP_FLAG] = 0 // ), // //若选择BRAND, // DS = "Retail(Quarterly)" // && ( // ISFILTERED ( TD_EXTERNAL_PACKINFO[PROD_COD] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PROD_CORP] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PROD_DES] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PROD_DES_C] ) // ), // CALCULATE ( // [External.PDOT.LY.MTH.MID], // TF_EXTERNAL_SALES[PROD_FLAG] IN { 1, 2 }, // TF_EXTERNAL_SALES[DTP_FLAG] = 0 // ), CALCULATE ( [External.PDOT.LY.MTH.MID_THC2], Fact_Sales[DTP_FLAG] = 0 ) ) ``` displayFolder: 02.Sales LY\币种KPI LY lineageTag: 7f49114d-47ca-42cd-a7ea-a8b8326d17fd measure 'External.PDOT.LY.MTH.MID_THC2' = SWITCH ( TRUE (), ISFILTERED ( Dim_Org ) && ISFILTERED ( Dim_GeoGroup ), BLANK (), ISFILTERED( Dim_Org ) && NOT ISFILTERED( Dim_Market) && NOT ISFILTERED(Dim_BrandDefaultMKT),BLANK(), [External.PDOT.LY.MID_THC1] ) displayFolder: 02.Sales LY\币种KPI LY lineageTag: 90b43786-32a4-4a59-8608-8ccb084448ac annotation PBI_FormatHint = {"isGeneralNumber":true} measure 'External.Prescription.LY.MTH_THC1' = ``` VAR MKT = VALUES(Dim_Market[MKT_SOURCE]) VAR AUDIT = CALCULATETABLE(VALUES(Dim_RC[AUDIT_SOURCE]),Dim_RC[MKT_SOURCE] IN MKT) // VAR INST = CALCULATETABLE(VALUES(Dim_IsTarget[INST_SOURCE]),Dim_IsTarget[MKT_SOURCE] IN MKT) VAR DEPT = CALCULATETABLE(VALUES(Dim_CoreDept[DEPT_SOURCE]),Dim_CoreDept[MKT_SOURCE] IN MKT) RETURN SWITCH ( TRUE (), ( ISFILTERED ( Dim_Market ) || ISFILTERED ( Dim_BrandDefaultMKT ) ) && ISFILTERED ( Dim_Org ), CALCULATE( SUMX ( Fact_Sales_THC, Fact_Sales_THC[PRESCRIPTION_LY] ), TREATAS(AUDIT,Dim_GeoKey[AUDIT_SOURCE]) ), ( ISFILTERED ( Dim_Market ) || ISFILTERED ( Dim_BrandDefaultMKT ) ) && (--ISFILTERED ( Dim_IsTarget ) || ISFILTERED(Dim_HP_Potential_AIA)), CALCULATE( SUMX ( Fact_Sales_THC, Fact_Sales_THC[PRESCRIPTION_LY] ) // , // TREATAS(INST,Dim_Institution[INST_SOURCE]) ), ( ISFILTERED ( Dim_Market ) || ISFILTERED ( Dim_BrandDefaultMKT ) ) && ISFILTERED ( Dim_CoreDept ), CALCULATE( SUMX ( Fact_Sales_THC, Fact_Sales_THC[PRESCRIPTION_LY] ), TREATAS(DEPT,Dim_Dept[DEPT_SOURCE]) ), SUMX ( Fact_Sales_THC, [PRESCRIPTION_LY] ) ) ``` displayFolder: 02.Sales LY\币种KPI LY lineageTag: ecc7cc12-4756-457e-9eed-d4b408e77aeb annotation PBI_FormatHint = {"isGeneralNumber":true} measure 'External.Unit.LY.MID_THC1' = ``` VAR MKT = VALUES(Dim_Market[MKT_SOURCE]) VAR AUDIT = CALCULATETABLE(VALUES(Dim_RC[AUDIT_SOURCE]),Dim_RC[MKT_SOURCE] IN MKT) // VAR INST = CALCULATETABLE(VALUES(Dim_IsTarget[INST_SOURCE]),Dim_IsTarget[MKT_SOURCE] IN MKT) VAR DEPT = CALCULATETABLE(VALUES(Dim_CoreDept[DEPT_SOURCE]),Dim_CoreDept[MKT_SOURCE] IN MKT) RETURN SWITCH ( TRUE (), ( ISFILTERED ( Dim_Market ) || ISFILTERED ( Dim_BrandDefaultMKT ) ) && ISFILTERED ( Dim_Org ), CALCULATE( SUMX ( Fact_Sales_THC, Fact_Sales_THC[SALES_UNIT_CAL_LY] * [External.SelectUnitMKTRatio] * [External.SelectRegionMKTRatio] ), TREATAS(AUDIT,Dim_GeoKey[AUDIT_SOURCE]) ), ( ISFILTERED ( Dim_Market ) || ISFILTERED ( Dim_BrandDefaultMKT ) ) && (--ISFILTERED ( Dim_IsTarget ) || ISFILTERED(Dim_IsTarget_AIASpecial) || ISFILTERED(Dim_HP_Potential_AIA)), CALCULATE( SUMX ( Fact_Sales_THC, Fact_Sales_THC[SALES_UNIT_CAL_LY] * [External.SelectUnitMKTRatio] ) // , // TREATAS(INST,Dim_Institution[INST_SOURCE]) ), ( ISFILTERED ( Dim_Market ) || ISFILTERED ( Dim_BrandDefaultMKT ) ) && ISFILTERED ( Dim_CoreDept ), CALCULATE( SUMX ( Fact_Sales_THC, Fact_Sales_THC[SALES_UNIT_CAL_LY] * [External.SelectUnitMKTRatio] ), TREATAS(DEPT,Dim_Dept[DEPT_SOURCE]) ), ( ISFILTERED ( Dim_Market ) || ISFILTERED ( Dim_BrandDefaultMKT ) || ISFILTERED ( Dim_MarketTA ) || ISFILTERED(Dim_Class)), SUMX ( Fact_Sales_THC, Fact_Sales_THC[SALES_UNIT_CAL_LY] * [External.SelectUnitMKTRatio] ), SUMX ( Fact_Sales_THC, [SALES_UNIT_CAL_LY] ) ) ``` displayFolder: 02.Sales LY\币种KPI LY lineageTag: 4dbdbff0-7962-4b2e-8671-6a20f1a19c16 annotation PBI_FormatHint = {"isGeneralNumber":true} measure 'External.Unit.LY.MTH_THC3' = ``` VAR TA = SELECTEDVALUE ( Dim_MarketTA[TA] ) VAR DS = SELECTEDVALUE ( Dim_DataSource[SOURCENAME] ) VAR MKTTA = MAX(Dim_Market[TA]) RETURN SWITCH ( TRUE (), //Retail 有一个TA是单独的，买的DTP数据 DS = "Retail" && ISFILTERED(Dim_MarketTA) && TA = "ONCO", CALCULATE ( [External.Unit.LY.MTH.MID_THC2], Fact_Sales[DTP_FLAG] = 1 ), DS = "Retail" && MKTTA = "ONCO", CALCULATE ( [External.Unit.LY.MTH.MID_THC2], Fact_Sales[DTP_FLAG] = 1 ), // //若选择PACKAGE,不选择地理维度，1表示pack+省份数据；2表示pack+全国的数据 // DS = "Retail(Quarterly)" // && ( // ISFILTERED ( TD_EXTERNAL_PACKINFO[PACK_DES] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PROD_PACK] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PACK_COD] ) // ) // && NOT ISFILTERED ( TD_EXTERNAL_GEO_Analysis ) // && NOT ISFILTERED ( TD_EXTERNAL_GEO_MID ), // CALCULATE ( // [External.Unit.LY.MTH.MID], // TF_EXTERNAL_SALES[PACK_FLAG] IN { 1, 2 }, // TF_EXTERNAL_SALES[DTP_FLAG] = 0 // ), // //若选择PACKAGE,且省份维度是全国 // DS = "Retail(Quarterly)" // && ( // ISFILTERED ( TD_EXTERNAL_PACKINFO[PACK_DES] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PROD_PACK] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PACK_COD] ) // ) // && SELECTEDVALUE ( TD_EXTERNAL_GEO_Analysis[PROVINCE_C] ) = "全国", // CALCULATE ( // [External.Unit.LY.MTH.MID], // TF_EXTERNAL_SALES[PACK_FLAG] IN { 1, 2 }, // TF_EXTERNAL_SALES[DTP_FLAG] = 0 // ), // //若选择PACKAGE,且省份维度不是全国 // DS = "Retail(Quarterly)" // && ( // ISFILTERED ( TD_EXTERNAL_PACKINFO[PACK_DES] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PROD_PACK] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PACK_COD] ) // ) // && SELECTEDVALUE ( TD_EXTERNAL_GEO_Analysis[PROVINCE_C] ) <> "全国", // CALCULATE ( // [External.Unit.LY.MTH.MID], // TF_EXTERNAL_SALES[PACK_FLAG] = 1, // TF_EXTERNAL_SALES[DTP_FLAG] = 0 // ), // //若选择地理,且省份维度不是全国 // DS = "Retail(Quarterly)" // && SELECTEDVALUE ( TD_EXTERNAL_GEO_Analysis[PROVINCE_C] ) <> "全国" // && ( // ISFILTERED ( TD_EXTERNAL_GEO_Analysis ) || ISFILTERED ( TD_EXTERNAL_GEO_MID ) // ), // CALCULATE ( // [External.Unit.LY.MTH.MID], // TF_EXTERNAL_SALES[GEO_FLAG] = 1, // TF_EXTERNAL_SALES[DTP_FLAG] = 0 // ), // //若选择BRAND, // DS = "Retail(Quarterly)" // && ( // ISFILTERED ( TD_EXTERNAL_PACKINFO[PROD_COD] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PROD_CORP] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PROD_DES] ) // || ISFILTERED ( TD_EXTERNAL_PACKINFO[PROD_DES_C] ) // ), // CALCULATE ( // [External.Unit.LY.MTH.MID], // TF_EXTERNAL_SALES[PROD_FLAG] IN { 1, 2 }, // TF_EXTERNAL_SALES[DTP_FLAG] = 0 // ), CALCULATE ( [External.Unit.LY.MTH.MID_THC2], Fact_Sales[DTP_FLAG] = 0 ) ) ``` displayFolder: 02.Sales LY\币种KPI LY lineageTag: 240dd32c-d5c5-409e-9d13-0d92af0b7878 measure 'External.Unit.LY.MTH.MID_THC2' = SWITCH ( TRUE (), ISFILTERED ( Dim_Org ) && ISFILTERED ( Dim_GeoGroup ), BLANK (), ISFILTERED( Dim_Org ) && NOT ISFILTERED( Dim_Market) && NOT ISFILTERED(Dim_BrandDefaultMKT),BLANK(), [External.Unit.LY.MID_THC1] ) displayFolder: 02.Sales LY\币种KPI LY lineageTag: 81387731-dd95-4602-80fa-70e8481c1698 annotation PBI_FormatHint = {"isGeneralNumber":true} measure 'External.Gr%.KPI.Market_THC' = VAR DS = SELECTEDVALUE(Dim_DataSource[SOURCENAME]) VAR YEARS = MAX(Dim_Calendar[YEAR]) RETURN SWITCH(TRUE(), //DS = "EC" && YEARS < 2023,BLANK(), [External.Sales.LY.KPI.Market_THC2] <> BLANK(), //ROUND([External.Sales.LY.KPI.Market],0) = 0,BLANK(), DIVIDE([External.Sales.KPI.Market_THC2]-[External.Sales.LY.KPI.Market_THC2],[External.Sales.LY.KPI.Market_THC2]) ) formatString: 0.0%;-0.0%;0.0% displayFolder: 03.Market Size lineageTag: b7c1c73d-2921-4749-ab56-19137b8dd8dd measure 'External.Sales.KPI.Market_THC2' = ``` VAR PV = [External.Sales.MTH_THC_4] VAR CV = [External.MKTPack.Count] VAR T = CALCULATE ( [External.Sales.KPI.Market.MID_THC1], ALL ( Dim_PackInfo ), ALL ( Dim_Corp ), ALL ( Dim_Class ), ALL ( Dim_MKTPackMapping[KEY_COMPETITOR] ) ) RETURN SWITCH(TRUE(), ISFILTERED(Dim_CoreDept) || ISFILTERED(Dim_Dept) || ISFILTERED(Dim_HP_Level) || ISFILTERED(Dim_Reimburse) || ISFILTERED(Dim_PrescriptionSource),IF(PV <> BLANK(),T), IF(CV <> BLANK(),T) ) // VAR PACK = // CALCULATETABLE ( // VALUES ( Dim_PackInfo[PACK_SOURCE] ), // ALLSELECTED ( Dim_PackInfo ) // ) // VAR CLASS = // CALCULATETABLE ( // VALUES ( Dim_Class[CLASS_SOURCE] ) // ) // VAR R = // CALCULATE ( // [External.Sales.KPI.Market.MID], // ALL ( Dim_PackInfo ), // ALL ( Dim_Corp ), // ALL ( Dim_Class ) // ) // RETURN // SWITCH ( // TRUE (), // ISFILTERED ( Dim_Corp ), IF ( [External.Sales.KPI.Market.MID] <> BLANK (), R ), // NOT ( ISEMPTY ( INTERSECT ( PACK, VALUES ( Dim_PackInfo[PACK_SOURCE] ) ) ) ) // && NOT ( // ISEMPTY ( INTERSECT ( CLASS, VALUES ( Dim_Class[CLASS_SOURCE] ) ) ) // ), R // ) ``` displayFolder: 03.Market Size lineageTag: 5059b184-8114-4060-90dd-98c02a178093 measure 'External.Sales.KPI.Market.MID_THC1' = [External.Sales.KPI_THC6] // [4External.Sales.KPI_THC] displayFolder: 03.Market Size lineageTag: d79abaa2-6a6c-4b9f-94df-19b839832bb3 annotation PBI_FormatHint = {"isGeneralNumber":true} measure 'External.Sales.LY.KPI.Market_THC2' = ``` VAR PV = [External.Sales.LY.MTH_THC4] VAR CV = [External.MKTPack.Count] VAR T = CALCULATE ( [External.Sales.LY.KPI.Market.MID_THC1], ALL ( Dim_PackInfo ), ALL ( Dim_Corp ), ALL ( Dim_Class ), ALL ( Dim_MKTPackMapping[KEY_COMPETITOR] ) ) RETURN SWITCH(TRUE(), ISFILTERED(Dim_CoreDept) || ISFILTERED(Dim_Dept) || ISFILTERED(Dim_HP_Level) || ISFILTERED(Dim_Reimburse) || ISFILTERED(Dim_PrescriptionSource),IF(PV <> BLANK(),T), IF(CV <> BLANK(),T) ) // VAR PACK = // CALCULATETABLE ( // VALUES ( Dim_PackInfo[PACK_SOURCE] ), // ALLSELECTED ( Dim_PackInfo ) // ) // VAR CLASS = // CALCULATETABLE ( // VALUES ( Dim_Class[CLASS_SOURCE] ) // ) // VAR R = // CALCULATE ( // [External.Sales.KPI.Market.MID], // ALL ( Dim_PackInfo ), // ALL ( Dim_Corp ), // ALL ( Dim_Class ) // ) // RETURN // SWITCH ( // TRUE (), // ISFILTERED ( Dim_Corp ), IF ( [External.Sales.KPI.Market.MID] <> BLANK (), R ), // NOT ( ISEMPTY ( INTERSECT ( PACK, VALUES ( Dim_PackInfo[PACK_SOURCE] ) ) ) ) // && NOT ( // ISEMPTY ( INTERSECT ( CLASS, VALUES ( Dim_Class[CLASS_SOURCE] ) ) ) // ), R // ) ``` displayFolder: 04.Market Size LY lineageTag: 3908f256-85b2-4a20-9a6c-942cecc1023a measure 'External.Sales.LY.KPI.Market.MID_THC1' = [External.Sales.LY.MTH_THC4] displayFolder: 04.Market Size LY lineageTag: df0aa79b-e169-4ca0-a00d-34f15ff8fb87 annotation PBI_FormatHint = {"isGeneralNumber":true} measure 'External.△MS%Rank.KPI.Combo_THC1' = CALCULATE([External.Rank.KPI.Combo_THC2], SAMEPERIODLASTYEAR(Dim_Calendar[DATE])) - [External.Rank.KPI.Combo_THC2] displayFolder: 05.Rank lineageTag: dbabf1fb-5078-4e0d-9b1f-ebcf05207032 measure 'External.Rank.KPI.Class_THC1' = ``` IF([External.Sales.KPI_THC6]<>BLANK(), CALCULATE(RANK(DENSE, ALL(Dim_Class[CLASS]), ORDERBY([External.Sales.KPI_THC6], DESC)), ALL(Dim_Corp) ) ) ``` displayFolder: 05.Rank lineageTag: 0d98b5b5-2101-4745-87f6-36cfaea15e29 annotation PBI_FormatHint = {"isGeneralNumber":true} measure 'External.Rank.KPI.CMPS_THC1' = ``` IF([External.Sales.KPI_THC6]<>BLANK(), CALCULATE(RANK(DENSE, ALL(Dim_PackInfo[CMPS_DES_C]), ORDERBY([External.Sales.KPI_THC6], DESC)), ALL(Dim_Corp) ) ) ``` displayFolder: 05.Rank lineageTag: 6f7fe269-11fd-4f93-8a34-730f76d567c6 annotation PBI_FormatHint = {"isGeneralNumber":true} measure 'External.Rank.KPI.Combo_THC2' = SWITCH(TRUE(), ISINSCOPE(Dim_PackInfo[MOLECULE_CODE_DESC]), [External.Rank.KPI.MolENCN_THC1], ISINSCOPE(Dim_PackInfo[PACK_DES]), [External.Rank.KPI.ProdPackCorp_THC1], ISINSCOPE(Dim_PackInfo[PROD_DES_C]), [External.Rank.KPI.ProdCorpMol_THC1], ISINSCOPE(Dim_PackInfo[CMPS_DES_C]), [External.Rank.KPI.CMPS_THC1], ISINSCOPE(Dim_Class[Class]), [External.Rank.KPI.Class_THC1], ISINSCOPE(Dim_PackInfo[CORP_DES_C]), [External.Rank.KPI.Corp_THC1], BLANK() ) displayFolder: 05.Rank lineageTag: 273438ca-6d1e-42c9-b6cb-47ebe238101a measure 'External.Rank.KPI.Corp_THC1' = ``` IF([External.Sales.KPI_THC6]<>BLANK(), CALCULATE(RANK(DENSE, ALL(Dim_PackInfo[CORP_DES_C]), ORDERBY([External.Sales.KPI_THC6], DESC)), REMOVEFILTERS(Dim_Corp[CORP_TYPE]),REMOVEFILTERS(Dim_PackInfo[CORP_TYPE]) ) ) ``` displayFolder: 05.Rank lineageTag: fbd2ed3d-23eb-4712-9081-d9889cfa218a annotation PBI_FormatHint = {"isGeneralNumber":true} measure 'External.Rank.KPI.MolENCN_THC1' = ``` IF([External.Sales.KPI_THC6]<>BLANK(), CALCULATE(RANK(DENSE, ALL(Dim_PackInfo[MOLECULE_CODE_DESC]), ORDERBY([External.Sales.KPI_THC6], DESC)), ALL(Dim_Corp) ) ) ``` displayFolder: 05.Rank lineageTag: 1f295fd7-51f2-4b21-93b5-3836cd18a2fc annotation PBI_FormatHint = {"isGeneralNumber":true} measure 'External.Rank.KPI.ProdCorpMol_THC1' = ``` VAR T = CALCULATETABLE(SUMMARIZE(Fact_Sales,Dim_PackInfo[CORP_DES_C],Dim_PackInfo[PROD_DES_C],Dim_PackInfo[CMPS_DES_C]),ALL()) RETURN IF([External.Sales.KPI_THC6]<>BLANK(), RANK(DENSE, T, ORDERBY([External.Sales.KPI_THC6], DESC)) ) ``` displayFolder: 05.Rank lineageTag: 56522d77-7d26-4578-b587-370a2b73b2bb annotation PBI_FormatHint = {"isGeneralNumber":true} measure 'External.Rank.KPI.ProdPackCorp_THC1' = ``` VAR T = CALCULATETABLE(SUMMARIZE(Fact_Sales,Dim_PackInfo[CORP_DES_C],Dim_PackInfo[PROD_DES_C],Dim_PackInfo[PACK_DES]) ,ALL()) RETURN IF([External.Sales.KPI_THC6]<>BLANK(), RANK(DENSE, T, ORDERBY([External.Sales.KPI_THC6], DESC))) ``` displayFolder: 05.Rank lineageTag: ed8bbaf3-7e59-4fa1-bc35-80baaad07d85 annotation PBI_FormatHint = {"isGeneralNumber":true} measure 'External.△MS%.KPI.Class_THC2' = VAR CV = [External.MS%.KPI.Class_THC1] VAR PV = [External.MS%.LY.KPI.Class_THC1] RETURN SWITCH(TRUE(), ISFILTERED(Dim_DataSource) && "AIA" IN VALUES(Dim_DataSource[SOURCENAME]) && PV <> BLANK(), CV - PV, ISFILTERED(Dim_DataSource) && "AIA" IN VALUES(Dim_DataSource[SOURCENAME]),BLANK(), CV - PV) formatString: 0.0%;-0.0%;0.0% displayFolder: 06.Class lineageTag: 6995a922-c29a-4222-8bd6-09b766abbb7b measure 'External.Gr%.KPI.Class_THC1' = ``` DIVIDE([External.Sales.KPI.Class_THC1]-[External.Sales.LY.KPI.Class_THC1],[External.Sales.LY.KPI.Class_THC1]) ``` formatString: 0.0%;-0.0%;0.0% displayFolder: 06.Class lineageTag: 8ccbb784-ed76-49f7-ba9a-5a51a2bf535d measure 'External.MS%.KPI.Class_THC1' = DIVIDE([External.Sales.KPI_THC6],[External.Sales.KPI.Class_THC1]) formatString: 0.0%;-0.0%;0.0% displayFolder: 06.Class lineageTag: 5a276557-c191-4300-8032-ad35298c6424 measure 'External.MS%.LY.KPI.Class_THC1' = DIVIDE([External.Sales.LY.KPI_THC6],[External.Sales.LY.KPI.Class_THC1]) displayFolder: 06.Class lineageTag: 9494f22c-cea3-48b3-8768-2bd0714c8464 measure 'External.PriceCU.KPI_THC1' = DIVIDE([External.Value.MTH_THC_3],[External.CountingUnit.MTH_THC3]) formatString: 0.0%;-0.0%;0.0% displayFolder: 06.Class lineageTag: 90c594c1-c039-468d-b93d-f0e4413be6e4 measure 'External.PriceUnit.KPI_THC1' = DIVIDE([External.Value.MTH_THC_3],[External.Unit.MTH_THC3]) formatString: 0.0%;-0.0%;0.0% displayFolder: 06.Class lineageTag: 7a8b116b-253a-44a3-b85b-a75f25e0b028 measure 'External.Sales.KPI.Class_THC1' = IF ( ISFILTERED ( Dim_MarketTA ) || ISFILTERED ( Dim_Market ) || ISFILTERED ( Dim_BrandDefaultMKT ), IF ( [External.Sales.KPI.Market_THC2] <> 0, VAR MKT = MAX(Dim_Market[MKT_SOURCE]) VAR CLASS = CALCULATETABLE(VALUES ( Dim_MKTPackMapping[CLASS_SOURCE] ),Dim_MKTPackMapping[MKT_SOURCE] = MKT) RETURN CALCULATE ( [External.Sales.KPI_THC6], ALL ( Dim_PackInfo ), ALL ( Dim_Corp ), KEEPFILTERS ( Dim_Market[Market] <> BLANK () ), TREATAS(VALUES(Dim_MKTPackMapping[Class]), Dim_Class[CLASS]) ) ) ) formatString: #,0.00 displayFolder: 06.Class lineageTag: 1fc27c9e-9db7-4d77-88f6-93ae270a99ab measure 'External.Sales.LY.KPI.Class_THC1' = IF ( ISFILTERED ( Dim_MarketTA ) || ISFILTERED ( Dim_Market ) || ISFILTERED ( Dim_BrandDefaultMKT ), IF ( [External.Sales.LY.KPI.Market_THC2] <> 0, VAR MKT = MAX(Dim_Market[MKT_SOURCE]) VAR CLASS = CALCULATETABLE(VALUES ( Dim_MKTPackMapping[CLASS_SOURCE] ),Dim_MKTPackMapping[MKT_SOURCE] = MKT) RETURN CALCULATE ( [External.Sales.LY.KPI_THC6], ALL ( Dim_PackInfo ), ALL ( Dim_Corp ), KEEPFILTERS ( Dim_Market[Market] <> BLANK () ), TREATAS(VALUES(Dim_MKTPackMapping[Class]), Dim_Class[CLASS]) ) ) ) formatString: #,0.00 displayFolder: 06.Class lineageTag: 9dd4ee00-3e46-4eb2-b417-56c31bbea80c column z dataType: string isHidden lineageTag: d70e697f-0879-463f-891a-03c246f31410 summarizeBy: none sourceColumn: z changedProperty = IsHidden annotation SummarizationSetBy = Automatic partition 'Measure THC' = m mode: import queryGroup: FreeReport独有表 source = let 源 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i44FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"." = _t]), 更改的类型 = Table.TransformColumnTypes(源,{{".", type text}}), 重命名的列 = Table.RenameColumns(更改的类型,{{".", "z"}}) in 重命名的列 annotation PBI_NavigationStepName = 导航 annotation PBI_ResultType = Table