How to show a distribution of total number of units sold

[Anonymous]

I'm pretty new to Power BI and I've combed through the forums but I can't seem to figure out how to do a couple of things.

I've got a file of sales data for the year and I'm trying to show the breakdown of how many customers have purchased <5, between 5 and 10, and >10 of particular items.  Using this sample data:

Company Name	Order Quantity	Item
AA	1	Apple
AA	2	Orange
AA	2	Pear
AA	1	Apple
BB	4	Apple
BB	5	Orange
BB	6	Pear
BB	1	Apple
CC	13	Pear
CC	15	Apple
CC	2	Apple
CC	1	Pear
DD	4	Apple
DD	5	Orange
DD	6	Pear
DD	1	Apple
EE	13	Pear
EE	6	Orange
EE	2	Orange

I can create this bar chart:

But what I'm really interested in is the number of companies that have purchased Apples or Oranges (I don't care about Pears).  So for example:

Company Name	Total of Apple and Oranges
AA	4
BB	10
CC	17
DD	10
EE	8

I want to show it like this:

I'm also concerned with the most common scenario, e.g. most of my customers have purchased "10" and it is a combination of both Apples and Oranges.

Any suggestions to set me on the right track are welcome.

Thanks

[v-stephen-msft · Accepted solution]

Hi @Anonymous ,

Please see the results first.

Here are the steps.

1.Create a table by entering data.

2.Create measures.

Total of Apple and Oranges = 
CALCULATE (
    SUM ( 'Table'[Order Quantity] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        [Company Name] = MAX ( 'Table'[Company Name] )
    )
)

Number of companies = 
SWITCH (
    MAX ( 'Table (2)'[Have purchases totalling] ),
    "<5",
        CALCULATE (
            DISTINCTCOUNT ( 'Table'[Company Name] ),
            FILTER ( ALLSELECTED ( 'Table' ), [Total of Apple and Oranges] < 5 )
        ),
    "b/w 5-10(incl)",
        CALCULATE (
            DISTINCTCOUNT ( 'Table'[Company Name] ),
            FILTER (
                ALLSELECTED ( 'Table' ),
                [Total of Apple and Oranges] >= 5
                    && [Total of Apple and Oranges] <= 10
            )
        ),
    ">10",
        CALCULATE (
            DISTINCTCOUNT ( 'Table'[Company Name] ),
            FILTER ( ALLSELECTED ( 'Table' ), [Total of Apple and Oranges] > 10 )
        )
)

You could download the attachment for details.

Best Regards,

Stephen Tao

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

[truptis]

@Anonymous 
What I need is total number of fruit by company so I can see if the company has purchased <5, b/w 5 and 10, or >10:
Result = var totalcount = calculate(sum([order quntity]), filter(tablename, EARLIER(tablename[Company name])= tablename[Company name]))

--above calculation is for total number of fruit by company
RETURN
If(sum[order quantity] > 5, ">5", if(sum[order quantity] >=5 && sum[order quantity] <=10, "between 5 & 10", "<5"))
-- this is for if the company has purchased <5, b/w 5 and 10, or >10

[truptis]

@Anonymous -> Do one thing, make a pivot here(Go to transform data and you can see Pivot/Unpivot icon) so that the Company name comes in a single row and Apply, Orange and Pear come in column. 

after that you can add a new conditional column which says if Apple || Orange then 1 else if Apple & Orange then 0 and so on. 

[truptis]

@Anonymous -> You didn't mention that you need it on a monthly level. Although, you can drag a slicer with the Month field in it and the numbers would match. 

[Anonymous]

I don't need it on a monthly level, I was using the monthly purchase as an example.  What I need is total number of fruit by company so I can see if the company has purchased <5, b/w 5 and 10, or >10.   In real life I'm looking at instrument sales, not fruit.  Each line item in my data file has a single instrument represented and there may have been multiple purchases of the instrument through the year.  At year end I want to know how many instruments the company has purchased and how many companies fall into the 3 tranches.

[truptis]

@Anonymous Use this logic:
Create a Flag column -> which says 1 if it is orange or apple & 0 if it is orange & apple
Flag = IF([item] = "Apple" && [item]= "Orange", 0, if([item]="Apple" || [item]="Orange",1,0))
 
Result_AppleOrOrange = CALCULATE(count(Company Name), filter(tablename,tablename[flag]=1))

[Anonymous]

In the example data file above each apple or orange would get a 1 and the pear gets the 0.  I need to tally by company name to see which companies have apple, orange or both.  Both will never be on the same line.

[truptis]

@Anonymous For companies having the purchange count < 5, use the below measure:

Have_purchasing_less_than_5= calculate(count(company name), filter(tablename, tablename[order] < 5))
For companies having the purchange count > 5, use the below measure:

Have_purchasing_greater_than_5= calculate(count(company name), filter(tablename, tablename[order] > 5))
For companies having the purchange count >=5 & =<10, use the below measure:

Have_purchasing_between5&10= calculate(count(company name), filter(tablename, tablename[order] >= 5 && tablename[order] <= 10))

@Anonymous -> Hope this works for you. If it does, then please hit the thumbs up & mark it as a solution. Thanks.

[Anonymous]

Ok, so I'm making progress.  And I truly appreciate the help!!! 

For the measure Have_purchasing_less_than_5, this does in fact give me row that has a <5 purchase, what it doesn't do is factor in Company xyz might have purchased 2 in Feb, 2 in March and 2 in April.  Their count is now 6 which is >5.

[truptis]

@Anonymous - For the companies that have ordered only Apples, use the below: 
Result_Apple = CALCULATE(count(Company Name), filter(tablename,tablename[item]="Apple"))
for distinct companies, use the below:
Result_Apple = CALCULATE(distinct(count(Company Name)), filter(tablename,tablename[item]="Apple"))

Similarly for Orange, you can replace "Apple" with "Orange"

[Anonymous]

Result_Apples = CALCULATE(DISTINCT(count('Sales Data'[Sold-to name])), FILTER('Sales Data', 'Sales Data'[Category]="Apples")) is returning an error message: The DISTINCT function expects a column reference expression or a table expression for argument '1'.

Additionally, when I get the syntax correct, this will return the number of companies that have ordered Apples and the other measure Result_Orange will return all companies that have ordered Oranges.  Most companies will have ordered both, I'm looking for the number of companies that have ordered one or the other, not both.

[truptis]

Hi @Anonymous ,

For the first scenario, use the below: 
Have_purchasing_total = var order = count(columnname)
return

if(Order < 5, "<5", If(Order >=5 && order <=10, "Between 5 & 10", ">10))

For companies purchasing Apples, use this measure:
Result_Apple = CALCULATE(sum(Order), filter(tablename,tablename[item]="Apple"))
For companies purchasing Oranges, use this measure:
Result_Orange = CALCULATE(sum(Order), filter(tablename,tablename[item]="Orange"))

@Anonymous - Please mark this as a solution and hit the thumbs up if it works for you. Thanks.

[Anonymous]

@truptis I'm not sure what to do with the first scenario.  Is this a new measure?

For the second scenario this gives me the total apples and the total oranges sold, I'm looking for the number of companies that have ordered only apples or only oranges.