Forum Discussion

GraceTCL's avatar
GraceTCL
Icon for Helper II rankHelper II
5 years ago

Segment by contribution is not working

Hi,

 

I am trying to segment customers (merchants) based on their % contribution to Sales (GMV)

I have created a "MexGroup" table with the segmentation and created a "GMVbyGroups" measure with formula below.

This is following example from https://www.youtube.com/watch?v=DxQX6K8L6hU.

 

However it is not working as no values return.

 
Could someone advise what is wrong with my formula? Thank you.
 
Formula:
GMVbyGroups =
Var RankingDimension = values(FoodMetrics[Merchant ID])
Var TotalMexBrands = CALCULATE(COUNTrows('dmerchant'),FILTER(all('dmerchant'[chain_name]),[GMV USD]>0))
Var MexBrandRank = [MexGMVRank]

Return
CALCULATE([GMV USD],
FILTER(RankingDimension,
Countrows(
Filter(MexGroups,
MexBrandRank > TotalMexBrands *MexGroups[Low]
&& MexBrandRank <= TotalMexBrands * MexGroups[High])) > 0))

1) 

Need Help

4 Replies

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi, GraceTCL 

     

    I don't quite understand your description,can you give a simple example or put a picture of the result you want to output?

     

    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      • GraceTCL's avatar
        GraceTCL
        Icon for Helper II rankHelper II
        5 years ago

         

         

        Hi v-yalanwu-msft 

         

        I am sorry. Actually I had meant that if I select the top X% of the customer by their count, the corresponding measure (GMV in this eg) and the customer name (Chain_Name) should appear.

         

        A sample output like this below. I have uploaded a new sample file with the relationship of the tables being more representative of my actual model. Pls refer to the tab "Here" where I have this unexpected this formula below that doesnt work out.

         

        Link to updated sample file: https://www.dropbox.com/s/s6ushpfeuo9sz3t/Food%20cleansed.pbix?dl=0

         

        MexGMVBySegment = 
Var RankingDimension = values(FoodMetrics[merchant_id])
Var TotalMexBrands = CALCULATE(COUNTrows('d_merchant'),FILTER('d_merchant',[GMV USD]>0))
Var MexBrandRank = [MexGMVRank]

Return
CALCULATE([GMV USD],
    FILTER(RankingDimension,
         Countrows(
             Filter(MexGroups,
                MexBrandRank > TotalMexBrands *MexGroups[Low]
                && MexBrandRank <= TotalMexBrands * MexGroups[High])) > 0))

         

         

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi GraceTCL ，

     

    You could modify measure by the following formula:

    GMVbygroup =
VAR _per =
    MAX ( 'MexGroups'[High] ) - MAX ( 'MexGroups'[Low] )
RETURN
    SWITCH (
        MAX ( 'MexGroups'[Group] ),
        "Top10%", [GMV LC] * _per,
        "11-20%", [GMV LC] * _per,
        "21-50%", [GMV LC] * _per,
        "Rest", [GMV LC] * _per)

    The final output is shown below:

    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.