Forum Discussion
Solved
Adding new column with a grouped value.
Hi everyone,
I have the following sample file:
| Segment | Country | Product | Discount Band |
| Government | Germany | Carretera | None |
| Government | Germany | Paseo | None |
| Government | Canada | Paseo | None |
| Government | Germany | Velo | None |
| Government | France | VTT | None |
| Government | France | Amarilla | None |
| Government | Mexico | Carretera | Low |
| Government | France | Carretera | Low |
| Government | France | Paseo | Low |
I want to do the following:
- Create additional column which includes distinct q-ty of products for each country. The result should be like in the table below: For each row of the table: If Country="Germany", then 3; If Country="France", then 4; If Country="Canada", then 1; If Country="Mexico", then 1.
Question: Is it possible to add such column to the existing table? How?
| Segment | Country | Product | Discount Band | Q-ty of products |
| Government | Germany | Carretera | None | 3 |
| Government | Germany | Paseo | None | 3 |
| Government | Canada | Paseo | None | 1 |
| Government | Germany | Velo | None | 3 |
| Government | France | VTT | None | 4 |
| Government | France | Amarilla | None | 4 |
| Government | Mexico | Carretera | Low | 1 |
| Government | France | Carretera | Low | 4 |
| Government | France | Paseo | Low | 4 |
- Create a measure which returns the amount of countries which have more than 1 product. The result should be 2 (Canada and Mexico)
Question: Is it possible to create a measure without creating new table.
I did it in the following way, but I want to improve it:
- Create a new table (answer to the first question):
NewTable =
VAR t1 =
SUMMARIZECOLUMNS('Sample'[Country] , 'Sample'[Product])
VAR t2 =
GROUPBY(t1,
'Sample'[Country],
"NUmofProducts", COUNTX(CURRENTGROUP(),1))
RETURN
t2
- Create a new measure for the NewTable (answer to the second question):
NewMeasure =
CALCULATE(COUNTX(NewTable,NewTable[NUmofProducts]),NewTable[NUmofProducts]>1)
How can I receive the same results without creating a new table?
Thanks in advance!
what's the purpose of create the addtional column? you didn't use it when you create a new table
you can try this measure
Measure =VAR tbl=SUMMARIZE('Sample','Sample'[Country],"product",count('Sample'[Product]))return COUNTROWS(FILTER(tbl,[product]>1))PavelAndreev You could create a new column in your existing table like this:
Column = COUNTROWS(DISTINCT(SELECTCOLUMNS(FILTER('Table',[Country]=EARLIER('Table'[Country])),"Product",[Product])))
5 Replies
ryan_mayuSuper User
what's the purpose of create the addtional column? you didn't use it when you create a new table
you can try this measure
Measure =VAR tbl=SUMMARIZE('Sample','Sample'[Country],"product",count('Sample'[Product]))return COUNTROWS(FILTER(tbl,[product]>1))- Greg_Deckler
Community Champion
PavelAndreev You could create a new column in your existing table like this:
Column = COUNTROWS(DISTINCT(SELECTCOLUMNS(FILTER('Table',[Country]=EARLIER('Table'[Country])),"Product",[Product])))Greg_Deckler , simply brilliant! Thanks a lot!
