Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
ppgandhi11
Helper V
Helper V

dynamic grouping question.

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-

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.2018-03-15_15-35-08.png

 

 

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")

2018-03-15_15-37-17.png

 

 

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

2018-03-15_10-59-29.png

 

 

 

 

View solution in original post

10 REPLIES 10
ppgandhi11
Helper V
Helper V

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
Not applicable

Why not just using a slicer chart to achieve this?

2018-03-15_10-59-29.png

 

 

 

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
Not applicable

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")

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
Not applicable

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.2018-03-15_15-35-08.png

 

 

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")

2018-03-15_15-37-17.png

 

 

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

2018-03-15_10-59-29.png

 

 

 

 

Anonymous
Not applicable

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?

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

@ppgandhi11,

 

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

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.