1 2 Previous Next 27 Replies Latest reply on Jun 8, 2015 11:54 AM by Alexander Mou

Grouping of Dataset based on then sum of a measure

I have a requirement to group orders based on the order amount (an order can have multiple lines). I want to group orders based on the order size.

Example of the Raw Data Example of Final View Is the 'Final view' achievable based on the raw data and using functions available in Tableau. If not, any other pointer towards implementation will be good.

• 1. Re: Grouping of Dataset based on then sum of a measure

Yes. Very achievable.

It is called histogram. The order categories are bins. CountD(Order Number) is the count.

• 2. Re: Grouping of Dataset based on then sum of a measure

can you supply a sample workbook?

• 3. Re: Grouping of Dataset based on then sum of a measure

Hi Jeffrey,

I have attached the sample workbook.

-Shijin

• 4. Re: Grouping of Dataset based on then sum of a measure

Hi Alexander

Please correct me if wrong. It is achievable easily if an order has only one line in the data set and if the bins are equal size.

In my case the data set is large and an order can have multiple lines.

Below is what i get If I use the bin concept. -Shijin

• 5. Re: Grouping of Dataset based on then sum of a measure

Hi Shijin,

You can achieve final you by creating calculated filed and place in row shlef

Cal Filed:

if [Amount \$]=<10 then "0-10\$"

ELSEIF [Amount \$]>11 and [Amount \$]<20 then "11-20\$"

ELSEIF [Amount \$]>21 and [Amount \$]<30 then "21-30\$" end

Use count as your measure aggregation.

For better solution, can you share workbook with sample data or data file

-

Sai

• 6. Re: Grouping of Dataset based on then sum of a measure

Hi Sai,

Below is what I get if I use your logic Also attached sample workbook.

-Shijin

• 7. Re: Grouping of Dataset based on then sum of a measure

try something like this.

• 8. Re: Grouping of Dataset based on then sum of a measure

Hi Jeffrey,

Thanks for trying to help but unfortunately that's not what I want. Thanks for your effort though.

-Shijin

• 9. Re: Grouping of Dataset based on then sum of a measure

so, what is it that you want?

• 11. Re: Grouping of Dataset based on then sum of a measure

Here is my solution workbook.

https://public.tableau.com/views/Sample1_81/Sheet1?:embed=y&:showTabs=y&:display_count=yes

See if it works for you.

On Tue, Jun 2, 2015 at 10:18 AM, Shijin Mathew <

1 of 1 people found this helpful
• 12. Re: Grouping of Dataset based on then sum of a measure

it is like your screenshot, it's just based of the data that you supplied in your sample workbook.

• 13. Re: Grouping of Dataset based on then sum of a measure

Hi Alex,

Thanks for the sample solution. The solution partly works for me.

The second column "size"  should be the count of orders  which has the  amount sum in that range.

In the example that you provided the size refers to the number of rows in the final table.

To make my request more clearer I added one more row to the raw data as below

suppose the raw data  was

 Order Number Amount(\$) Product Category 100 10 Low End 101 5 High End 102 3 High End 100 10 High End 103 7 Low End 102 17 High End 104 15 High End

I would like the end result to be

 Order Category(\$) Count of Order High End(\$) Low End(\$) 0-10 (\$) 2 5 7 10-20 (\$) 3 45 10 20-30 (\$) 0 0 0

You can see the column 'Count of order' column corresponding to "20-30 (\$)" has count 3 now because I added another order with amount 15.

-Shijin

• 14. Re: Grouping of Dataset based on then sum of a measure

Hi Jeff,

The second column "count of orders" should be the count of orders which has the amount sum in that range.

In the below example "count of orders" is 2 for each row since there are 2 orders whose sum of amount comes in each of that range - that is the part which I am having trouble implementing. 1 2 Previous Next