topic Re: How to sum up data having same label (DAX Query)? in DAX Commands and Tips

How to sum up data having same label (DAX Query)?

Anonymous — Wed, 17 Feb 2021 15:04:19 GMT

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.

Re: How to sum up data having same label (DAX Query)?

HotChilli — Wed, 17 Feb 2021 19:48:41 GMT

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.

Re: How to sum up data having same label (DAX Query)?

Anonymous — Thu, 18 Feb 2021 05:42:51 GMT

Umm, no.

I'll provide a snapshot and try to explain my problem in a better way.

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.

Re: How to sum up data having same label (DAX Query)?

stevedep — Thu, 18 Feb 2021 06:40:57 GMT

Hi,

Hope this code helps you to understand a potential solution using naturalinnerjoin. pls let me know if you have further questions.

Re: How to sum up data having same label (DAX Query)?

Anonymous — Thu, 18 Feb 2021 07:00:09 GMT

Nah, this isn't it.

Thanks for the effort though.

Re: How to sum up data having same label (DAX Query)?

stevedep — Thu, 18 Feb 2021 07:56:10 GMT

Perhaps this is what you are looking for:

DEFINE MEASURE 'Date'[Rank] = RANKX ( ALL ( 'Date'[Calendar Year Month] ), CALCULATE ( SUM ( Sales[Quantity] ) ),,,SKIP) MEASURE 'Date'[SalesPrevPeriod] = VAR _r = 'Date'[Rank] - 1 RETURN MAXX ( FILTER ( ADDCOLUMNS ( ALL ( 'Date'[Calendar Year Month] ), "sales", CALCULATE ( SUM ( Sales[Quantity] ) ), "rank", 'Date'[Rank] ), [rank] = _r ), [sales] ) EVALUATE ADDCOLUMNS ( VALUES ( 'Date'[Calendar Year Month] ), "sales", CALCULATE ( SUM ( Sales[Quantity] ) ), "t", [Rank], "salesprevperiod", [SalesPrevPeriod] ) ORDER BY [t]

See it working here.

Hope it helps.

Kind regards,

Steve.

Re: How to sum up data having same label (DAX Query)?

Anonymous — Thu, 18 Feb 2021 09:50:48 GMT

Could you explain the code?

I am not able to understand it in this context

Re: How to sum up data having same label (DAX Query)?

stevedep — Thu, 18 Feb 2021 09:58:50 GMT

In the example that I provide the column YearMonth represents Week in your case, sales represents visits and t (rank) represents Label.

You can create a measure in your dashboard that matches SalesPrevPeriod in the example.

So in your case you would search the value for the records where the label is one minus the current row value.

Hope this helps?

Re: How to sum up data having same label (DAX Query)?

Anonymous — Thu, 18 Feb 2021 17:21:41 GMT

Nope.

Your code does something entirely different. Here your rank is unique to every row i.e for every sales value.

What I need you to understand is, my table has millions of rows. So for instance for a given week, there are millions of data rows BUT they have a label and that's the same.

The given matrix I posted sums up the millions of the visits belonging to the respective fiscal week which itself has dates varying in the 7 days. That complicates the process. Moreover I can't run the code you posted since power bi is falling short of memory.

Re: How to sum up data having same label (DAX Query)?

stevedep — Thu, 18 Feb 2021 18:10:20 GMT

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490