Segment by contribution is not working

[GraceTCL]

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.

My sample file at link:  https://www.dropbox.com/s/hkhx0yziyakgtbn/Sample.pbix?dl=0 

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) 

[v-yalanwu-msft]

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.  

[v-yalanwu-msft]

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]

Hi @v-yalanwu-msft 

I am trying to segment the "GMV" value by the range. An intended output here in pic below based on sample data in Power BI file and corresponding workings for train of thoughts in excel at link. Hope this clarifies and you will be able to help. Thank you.

https://www.dropbox.com/scl/fi/rsoy72ni9hwdf99verz3o/exemplify.xlsx?dl=0&rlkey=a1m22k2cif4ej8shlimih...

[GraceTCL]

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))