dynamic grouping question.

[ppgandhi11]

Hi, I am very new to power bi, i think the task i am undertaking is too big. Here is what I want to do. I need some good start point.

I have a table with 2 columns. Products and sales. 1:many relationship (each product can have >= 1 rows). I am able to create a measure to get the product and total sale for it using group by.

Product   TotalSale

100         30000

101         20000

102         50000

:

:

Please note, the above is a measure and it prints correctly. I want to add a column to it with a logic:

If the TotalSale is >= 30000, call it high sale, otherwise call it low sale.

Product    TotalSale    Classification

100           30000          High Sale

101           20000          Low Sale

102           50000          High Sale

:

:

For this, I have created a look up table with 2 rows:

Group          Min    Max

Low Sale      0        29999

High Sale    30000  100000

I don't know how to achieve the 3rd column.

Finally, I need to create a tree map with only 2 classification: High Sale (collection of all the products that fall in this category) and Low Sale (collection of all the products that fall in low sale category).

How to achieve this? Am I on right path? I believe in order to achieve tree map with 2 classification I need additional 3rd column that tells me High/Low Sale.  Thanks bunches. Prashant-

[ppgandhi11]

I made some progress. I created a new measure called Group.

Group = if((calculate(sum(Source[Sales]), groupby(Source, Source[product])) >= 30000), "High", "Low")

It prints now below:

Product       Total  Sale        Group

100             30000                 High

101             20000                 Low

102             50000                 High

:

:

Now, I need to create a dynamic group that puts Products into either High or Low group sales.  Every product will need to fall into one of these 2 categories.

Thanks.

[Anonymous]

Why not just using a slicer chart to achieve this?

[ppgandhi111]

How would this work Jessica? The High Sale and Low Sale are not connected with the Product table anyhow. The post I had shows that they are coming from the measure called "Group".  

Can you please elaborate?  

[Anonymous]

If you only want to tag the sales range, no need to create a new table. Just to add a new column with a simple If function to do it. Like the example below

SalseRange = IF([TotalSale]<3000,"Low Sale", "High Sale")

[ppgandhi111]

How can I add a column with the IF condition you have mentioned Jessica?

Base table has only 2 columns Products and Sales with 1:many relationship. The Total Sales is achieved via a measure with the below formula.

TotalSaleMeasure = calculate(sum(sales), groupby(source, source[product]))

This measure gives me the Product and Total Sales in a tabular format.  Further, I was able to add the High/Low sale to it depending on the Total Sale amount using another measure.

[Anonymous · Accepted solution]

Hmmm, I may misunderstanding your case since I don't know your data structure. My suggestion is provided based on the following scenario.

There is a Product -> sales table like this. One product could have several sales value.

Then created a new table using the following Dax

Total Sales = GROUPBY('Product','Product'[Product],"Total Sales",SUMX(CURRENTGROUP(),'Product'[Sales]))

And then, created a new column to tag the product based on the total sales number

Sales Range = IF([Total Sales]<30000, "Low Sales", "High Sales")

After that, you could create a chart to show the High/Low Sales products.

[NitinJoshy]

Can this be achieved as a column in the original table? is there a way to achieve the grouping and bucketing in a single step?

[ppgandhi11]

Perfect! This is what I was looking for! Thanks bunches Jessica. Learnt about CurrentGroup() today. didn't know how to use that. 🙂

[v-chuncz-msft]

@ppgandhi11,

Glad to hear that. You may help accept solution. Your contribution is highly appreciated.

[Greg_Deckler]

Well, if TotalSale is a measure, then you should just need to create another measure like this:

Classification = IF([TotalSale]>=30000,"High Sale", "Low Sale")

I might be missing something.