Forum Discussion
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-
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.
10 Replies
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.
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.
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.
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.
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?
