Assign Column Value to a Variable

[bcastro19]

Hi, 

I am trying to create a variable that contains the values in one of my table columns. For example, I have column called Category Number which assigns a text column a number ranging from 1-13. I would like to create a variable that contains all 1-13 values. I thought I could use this as my variable:

VAR CAT = VALUES('2021_Data'[CAT])

I then want to create another variable: VAR CATSales = IF(CAT =1,

CALCULATE('2021_Data'[2021AllSales],FILTER('2021_Data','2021_Data'[Category Number]=1),... for each 13 of my categories. 

Ideally, returning CATSales would return 13 category sales. 

However, I am not able to do this. Anyone have any advice?

[v-eqin-msft · Accepted solution]

Hi @bcastro19 ，

VALUES() function returns a table.

You could add a column or create a measure to get the sum of sales:

Column = CALCULATE(SUM('2021_Data'[Sales]),FILTER('2021_Data',[CAT]=EARLIER('Table'[CAT])))

Measure = CALCULATE(SUM('2021_Data'[Sales]),FILTER('2021_Data',[CAT]=MAX('Table'[CAT])))

Output:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bcastro19]

Hi @v-eqin-msft this is exactly what I was looking for, thank you so much for your help!

[amitchandak]

@bcastro19 , If you looking to use slicer values then you can not do that in a new column

to you have 13 static columns

1= CALCULATE('2021_Data'[2021AllSales],FILTER('2021_Data','2021_Data'[Category Number]=1) )

or

you can measures

CALCULATE(sum('2021_Data'[2021AllSales]) ,FILTER('2021_Data','2021_Data'[Category Number]=1)

for measure you can use field parameters or calculation group to select

https://amitchandak.medium.com/power-bi-field-parameters-a-quick-way-for-dynamic-visuals-fc4095ae9af...

Calculation Groups- Measure Slicer, Measure Header Grouping, Measure to dimension conversion. Complex Table display : https://youtu.be/qMNv67P8Go0