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

[Anonymous]

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.

[stevedep]

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

[HotChilli]

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.

[Anonymous]

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.

[stevedep]

Hi,

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

[Anonymous]

Nah, this isn't it.

Thanks for the effort though.

[stevedep]

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. 

[Anonymous]

Could you explain the code?

I am not able to understand it in this context

[stevedep]

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?

[Anonymous]

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.