How to split in 3 categories the list of clients

[Marco_Natuzzi]

Hi guys I need help - Im gonna explain first on what consist my Power BI report:

It's a direct query report where I have the list of account and the related order flow generated by each account . Here the structure of my data :

The tables are the following:

> Fact_Sales here I have the table with all the sales generated (ofc each row represent a single order place by a customer)

> Fact_Budget_Wholesale here I have the budget that can be expressed at level of country manager / regional manager / area manager / time / Zone (geographical area) / channel (retail / wholesale) / client (accounts) / brand (for the four brand of the company)

> Dim_SD2_AreaManager / Dim_SD2_RegionalManager / Dim_SD2_CountryManager - those three tables represent the hierarchy of the organization

> Dim_TempoTAB useful to idenitfy the time period that we want to analyze

> Dim_ZoneNew identies the area that we want to analyze

> Dim_Canali for the channel where we are able to distribute our products  [retail / wholesale]

> Dim_Clienti here we have the clients

> Dim_Brand tabel that identifies the different brands

All the dimensions are realted to the 2 fact tables through a 1 to many relationship [resulting in a star schema].

I first explained to you how is structured the report [in direct query from sql server + some additional tables from dataverse, but I don't want to get into it for the moment].

My question / need is the following: Im structuring a report for our division in China where I have on the left all the Area Manager and on the right the list of account - ofc everytime I select a specific area manager appears the list of account. I need 2 things:

1. A measure that allows me to split the accounts into 3 categories 

> first catgorty 0 to 15 % of the total sales of accounts [ SellInFullValuta_Measure [that is the sum of the sales -->  SUM(SellInFull) ]

> Second category 15 to 45 % of the total sales of accounts

> Third category 45 % to 100 % of the total sales of accounts

OFC to detect this I can use the the Icon feature in the formatting section of the table - but is only partially useful since I cannot use it for writing any dax. SO what I need first is a measure that allows me to have this split in a column - consequently this measure based on the area manager that I select will give me a different split of the accounts

> 2nd considering that I will have this column - next step will be create a new measure that will give me new categories where - if tha account is in the 1st category and has negative trend compare with the previous year is an account with priority A, and so  on -> I know how to do this second clusterization after I will create the first measure.

Thank you so much in advance for the precious help

Marco 

[Marco_Natuzzi]

OR @Anonymous or anyone feel free to partecipate to the discussion another solutio can be split the list of accounts into 3 categories based on a simple math - since I have 27 clients under a specific area manager I have ranked them based on sales [here the formula to caluclate the rank based on the Sell IN : FULL  RANKX(ALL(Dim_Clienti[Cliente]),[SellInFullValuta_Measure])]. 

Here we have an example - I used the icon features (circle in red) - so i splitted in a mathematical way the clients in 3 equal part based on ranking that is referring to the sales.

OFC this icon cannot be used in any other DAX formula - would be possible have a measure that allows me to give those three categories that are reflected by the icons in this specific case ?

so  I'm gonna have 3 categories (Green Category 1 that is represented by the first 33 % of my 27 clients / Yellow Category 2 that is represented by the second 33 % of my 27 clients / Red Category 3  that is represented by my third 33 % of my 27 clients).

OFC this is gonna be a measure that will adapt to each case - so if I select another manager that has only 9 clients I will have the following partition :

[Anonymous]

Hi @Marco_Natuzzi 

Based on your description, if you want to split the measure by percentage, the SellInFullValuta_Measure is the sum sales,not the percentage of the sells. You need to create a new meausre to calculate the percentage.

a.Create a mesaure to calculate the total sales.

Total_sales =
CALCULATE ( SUM ( Fact_Sales[SellInFull] ), ALLSELECTED ( Fact_Sales ) )

b.Create a measure to calculate the percentage, and should put this measure to the conditinal formatting.

Sales_Percentage =
DIVIDE ( [SellInFullValuta_Measure], [Total_sales] )

c. Create the measure to split in a column

Per_split =
SWITCH (
    TRUE (),
    [Sales_Percentage] >= 0
        && [Sales_Percentage] < 0.15, "0~15%",
    [Sales_Percentage] >= 0.15
        && [Sales_Percentage] < 0.45, "15%~45%",
    [Sales_Percentage] >= 0.45, "45%~100%"
)

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Marco_Natuzzi]

Hi @Anonymous 

First of all thank you for your answer - was very insightful but did not correspond exactly to what I need - just to explain a little more :

> With your first 2 formulas I was able to have at the level of each single account the amount of total sales and the percentage of these sales over the total (highlighted in red the 2 columns)

> as a next step the categorization that is based on the amount of sales and ranking -> to explain a little bit more the top accounts which sums of Sales_Percentage_SellINFULL_Valuta represent the top 55 % of the sales should be CATEGORY 1 (here a screenshot of an excel where the column in red Cumulated Percentage represente the cumulative sum of the percentages in the column , while beside there is the "categorization" that allow us to identify the top accounts that represent the top 55 % of the sales, and the CATEGORY 2 that represent the accounts that represents the 55 % to the 85 %  and Category 3 the bottom 85 to 100 % of cumulative percentage [attached the screen of the excel with the result that i would like to achieve)

Thank you so much in advance for your support

[Anonymous]

Hi @Marco_Natuzzi 

For the first requirement: the cumulative sum of the percentages, You can refer to the follwing measure.

Cum_Sum =
SUMX (
    FILTER ( ALLSELECTED ( Fact_Sales ), [#] <= MAX ( Fact_Sales[#] ) ),
    [Sales_Percentage_SellINFULL_Valuta]
)

For the second requirement:the categorization , you can refer to the following measure.

Split =
SWITCH (
    TRUE (),
    [Cum_Sum] >= 0
        && [Cum_Sum] < 0.55, "Category1",
    [Cum_Sum] >= 0.55
        && [Cum_Sum] < 0.85, "Category2",
    [Cum_Sum] >= 0.85, "Category3"
)

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Marco_Natuzzi]

hI @Anonymous - 

I tought the same solution to obtain the cumulative sum:

Cumulative_Sum = SUMX(FILTER(ALLSELECTED(Fact_Sales),[Clients_Ranking_SellINFULLValuta]<= MAX([Clients_Ranking_SellINFULLValuta])),Fact_Sales[Sales_Percentage_SellInFullValuta])

[Clients_Ranking_SellINFULLValuta] is the name of the measure in my report that is in the column under the name "#" [highlighted by the red arrow]

 The problem is that the "#" is a measure that i Calculated in the follwing way (and is in the Table Dim_Clienti):

Clients_Ranking_SellINFULLValuta = RANKX(ALL(Dim_Clienti[Cliente]),[SellInFullValuta_Measure])

When i pass this measure into your foruma Cum_Sum it gives me error in the MAX function saying that "The MAX function accepts only a column reference as argument number 1." [I don't know why is giving me this error]

I think that the solution that you propose is in the right path but we have to solve this issue with the function MAX - I have no clue on how to obtain this cumulative sums. Any ideas ??

Thank you in advance for the response. and let me know if you or anyone else need furthermore information to find a solution.

Again thank you so much in advance

[Anonymous]

Hi @Marco_Natuzzi 

Try the following measure

Cumulative_Sum =
VAR a = [Clients_Ranking_SellINFULLValuta]
RETURN
    SUMX (
        FILTER ( ALLSELECTED ( Fact_Sales ), [Clients_Ranking_SellINFULLValuta] <= a ),
        Fact_Sales[Sales_Percentage_SellInFullValuta]
    )

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Marco_Natuzzi · Accepted solution]

HI @Anonymous  thank you for the support I just found a first solution to have the cumulative sales base on a category . here the code in case someone else has the same problem:

Cumulative Total RankX =

   IF(

      ISINSCOPE(Dim_Clienti[Cliente]),

       VAR CurrentRank = [Ranking_SellINFULLValuta]

       VAR RankkAndOrders =

            ADDCOLUMNS(

                ALLSELECTED(Dim_Clienti[Cliente]),

                 "@Ranking",[Ranking_SellINFULLValuta],

                 "@Sales", [SellInFullValuta_Measure]

            )

        VAR BetterCategory =

            FILTER (

                RankkAndOrders,

                [@Ranking]<=CurrentRank

            )

        VAR Result =

            SUMX(BetterCategory,[@Sales])

        RETURN

            Result)