复杂的占比

技巧是返回当前层级的字段，以及利用isfiltered判断层级的顺序

Value_For_Table:=
//计算逻辑：分子分母的取值会根据层级而变，在category或keyword层级求的是自家产品与市场全部的百分比，在F_Value[Competitor_NX_Base]或brand层级下的分母是这个层级的总数，百分比求的是自家产品或非其他产品与这一层的占比
//bns筛选后的逻辑：筛选某个brand NXGroup 或者SKu都会返回其所属的keyword，显示竞品的占比，而自家产品的值就只是新品的值
//bns筛选后，cns层级不足100%原因是，NX的部分少了，因为选择一个brand，层级只显示一个NX的brand，但是分母全部都算进去
VAR t =
    CALCULATE ( MIN ( D_Product[new_iNXtiative_sos_date] ) )
VAR d =
    DATE ( YEAR ( t ), MONTH ( t ), 1 )
VAR brand =
    VALUES ( D_Product[brand_name_en] )
VAR cate =
    VALUES ( D_Product[category_name_en] )
VAR manu =
    CALCULATE ( MAX ( F_Value[Competitor_NX_Base]), ALL ( D_Product ),ALL(F_Value[rank] ), ALL(D_Product[product_name_cn]),ALL(D_Product_Search[rpc_code]))
VAR keyword =
    CALCULATETABLE (
        VALUES ( F_Value[keyword] ),
        ALL(F_Value[Competitor_NX_Base]),
        ALL(F_Value[rank]),
        ALL ( F_Value[brand_en_org] )      
    )          //当bns筛选后，获取的keyword列值，用来判断每个brand所属的keyword


VAR x =
    IF (
        OR (
            ISFILTERED ( 'F_Value[brand_en_org] ), //2个isfiltered的顺序很重要
            ISFILTERED (F_Value[Competitor_NX_Base])
        ),
        IF (
           manu = "NX",
            CALCULATE ( SUM ( 'F_Value[metric_value] ) ),
            IF (
                AND (
                   OR( manu = "Competitor",manu="Base"),
                    CALCULATE (
                        MAX ( 'F_Value[keyword] ),
                        ALL ( D_Product ), ALL(D_Product_Search[rpc_code])
                    )
                        IN keyword          //判断bns筛选后，当前的brand的keyword是否属于这个keyword的列值
                ),
                CALCULATE (
                    SUM ( 'F_Value[metric_value] ),
                    ALL ( 'D_Product'[brand_name_en] ),
                    ALL ( D_Product[new_iNXtiative_project_name] ),
                    ALL ( D_Product[product_name_en] ),
                    ALL ( 'D_Product'[is_new_iNXtiative_flag] ),
                    ALL('D_Product'[category_name_en]), 
                    ALL(D_Product[product_name_cn]),
                    ALL(D_Product_Search[rpc_code])
                )
            )
        ),
        CALCULATE (
            SUM ( 'F_Value[metric_value] ),
           F_Value[Competitor_NX_Base]= "NX"
        )
    )
VAR y =
        CALCULATE (
            SUM ( 'F_Value[metric_value] ),
            ALL ( 'D_Product'[brand_name_en] ),
            ALL ( D_Product[new_iNXtiative_project_name] ),
            ALL ( D_Product[product_name_en] ),
            ALL ( 'D_Product'[is_new_iNXtiative_flag] ),
            ALL (F_Value[Competitor_NX_Base] ),
            ALL ( F_Value[brand_en_org] ),
            ALL(F_Value[rank]),
            ALL('D_Product'[category_name_en]),
             ALL(D_Product[product_name_cn]),
            ALL(D_Product_Search[rpc_code])
        )
    
VAR actual =
    DIVIDE ( x, y ) 
VAR sos =
CALCULATE(
    DIVIDE (
        //分子
       IF (
        OR (
            ISFILTERED ( 'F_Value[brand_en_org] ),
            ISFILTERED (F_Value[Competitor_NX_Base])
        ),
        IF (
           manu = "NX",
            CALCULATE ( SUM ( 'F_Value[metric_value] ) ),
            IF (
                AND (
                   OR( manu = "Competitor",manu="Base"),
                    CALCULATE (
                        MAX ( 'F_Value[keyword] ),
                        ALL ( D_Product )
                    )
                        IN keyword          //判断bns筛选后，当前的brand的keyword是否属于这个keyword的列值
                ),
                CALCULATE (
                    SUM ( 'F_Value[metric_value] ),
                    ALL ( 'D_Product'[brand_name_en] ),
                    ALL ( D_Product[new_iNXtiative_project_name] ),
                    ALL ( D_Product[product_name_en] ),
                    ALL ( 'D_Product'[is_new_iNXtiative_flag] ),
                    ALL('D_Product'[category_name_en]), 
                    ALL(D_Product[product_name_cn]),
                    ALL(D_Product_Search[rpc_code])
                )
            )
        ),
        CALCULATE (
            SUM ( 'F_Value[metric_value] ),
           F_Value[Competitor_NX_Base]= "NX"
        )
    ),
        //分母
        CALCULATE (
            SUM ( 'F_Value[metric_value] ),
            ALL ( 'D_Product'[brand_name_en] ),
            ALL ( D_Product[new_iNXtiative_project_name] ),
            ALL ( D_Product[product_name_en] ),
            ALL ( 'D_Product'[is_new_iNXtiative_flag] ),
            ALL (F_Value[Competitor_NX_Base] ),
            ALL ( F_Value[brand_en_org] ),
            ALL(F_Value[rank]),
            ALL('D_Product'[category_name_en]),
             ALL(D_Product[product_name_cn]),
            ALL(D_Product_Search[rpc_code])
        )
    
    ),FILTER ( ALL ( 'D_Date'[Date] ), 'D_Date'[Date] >= d ),
            ALL ( 'T_Slicer' )
        )

RETURN
     IF ( SELECTEDVALUE ( 'T_Slicer'[Time Period] ) = "SOS TD", sos, actual )