Top X % of total

[GraceTCL]

Hi

I am trying to determine top X % of customers to Sales (GMV) based on this link: https://www.youtube.com/watch?v=jyKAxKSHBTk

Formula works but I am trying to sum the GMV% contribution of these customers and it is not showing the sum but rather 100% as per screenshot.

Appreciate any help to correct my formula below.

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

Formula:

Top20%MexBrandsByGMV =

if(isblank([GMV USD]),

Blank(),

If(RANKX(all(dmerchant),[GMV USD], ,DESC) <

(CALCULATE([UniqueMexBrands], All(dmerchant))* 0.2),

[GMV USD],

Blank()

)

)

Where

UniqueMexBrands = 

= CALCULATE( DISTINCTCOUNT(dmerchant[Chain_name]),FoodMetrics)

[v-yalanwu-msft]

Hi @GraceTCL ,  

You could create a measure by the following formula: 

Step1: new table  

newtable = SUMMARIZE('dmerchant',[Chain_name],"_gmv",[GMV Total]) 

 Step2: new a measure 

total = SUM('newtable'[_gmv]) 

 Step3: modify the %GMVContribution  measure 

%GMVContribution = SUMX(SUMMARIZE('dmerchant',[Chain_name],"1",[Top20%MexBrandsByGMV]/[total]),[1]) 

 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.  

[AlB]

@GraceTCL 

I don't understand the question. Plus I don't see what this has to do with the solution I provided in my previous post

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[AlB]

Hi @GraceTCL 

Create a new measure based on the one you already have:

%GMVContribution TOT = 
SUMX( DISTINCT(dmerchant[Chain_name]), [%GMVContribution] )

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[GraceTCL]

Hi @AlB 

Thanks for your reply.

When I change "GMV Total" to this formula below, the Total % contribution of the MexBrands couldnt work anymore. Essentially, I want "GMV Total" to be still subjected to implicit context filters of vertical.

Formula:

GMV Total = CALCULATE([GMV USD],ALLEXCEPT(Vertical,Vertical[Vertical]))

Updated sample file here:

https://www.dropbox.com/s/hkhx0yziyakgtbn/Sample.pbix?dl=0

[GraceTCL]

Hi @v-yalanwu-msft 

Thanks for your reply. However, when I vary the time periods, the denominator is still the GMV number across the entire periods, hence distorting derived % contribution. Could you advise how and where can I vary the denominator GMV to change as well as per time period so that % contribution is correct?

See an unexpected output as per problem stated above where total GMV still remains at 402 which represents the full period but it should be smaller.