Forum Discussion
How to sum up data having same label (DAX Query)?
So this one is pretty tough to explain.
I have a table. It has tons of columns. Every row has an associated label (column) and multiple rows might have the same label.
So the issue is, when I represent the data in a matrix, I need to sum up data belonging to a label. How do I do that in DAX.
Example :
- There are 10 rows having a label ( a column ) as -1.
- There are also 10-20 rows having a label -2 and so on.
When I represent the data in a matrix, what I need is the summation of the data in the rows having label -2. For the next row in the matrix, I need the summation of the data having label -3 and so on.
I thought a dax query like :
Previous_week_data =
var check = MAX(table[label]) - 1
return
CALCULATE( SUM(table[data]), table[label] = check)
--------
But this isn't working in a matrix. It shows the latest data properly in a card but not in a matrix. It should work since I want to filter out only those data having a specific label and as the context row changes, the label should change.
Kindly help me.
9 Replies
A data sample would be nice (and desired output).
Are you summing the data in one column only? If so, it looks like you can create a measure = SUM(table[thecolumn]).
Put the label in the Rows of a matrix, the measure in the Values section.
Let me know if I've understood it correctly.
Umm, no.
I'll provide a snapshot and try to explain my problem in a better way.
Matrix in Power bi
So, as you can see every row in the matrix has an associated label. Which means in the dataset, for instance - for week : 2021-W23 (which is a text, not a date format) all the thousands of rows having visits data have a label of -31. The visits of the weeks are naturally summed up when I represent them in a matrix. Mind you I have done some computation to represent the label in the manner otherwise it was also being summed up.
So all I want is the visits value of the previous row in every row. So for 2021-W23, the prev_week will be blank. For 2021-W24, the prev_week should have the visits value of 2021-W23 and so on.
Kindly help me out.
