cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GraceTCL
Helper II
Helper II

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.

Capture.PNG

 
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) 

4 REPLIES 4
v-yalanwu-msft
Community Support
Community Support

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:

v-yalanwu-msft_1-1620899469331.png

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
Community Support
Community Support

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.

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

Output.PNG

 

 

 

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

 


 

Segment.PNG

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors