Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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 ...
Anonymous's avatar
Anonymous
Not applicable
5 years ago

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's avatar
stevedep
Icon for Memorable Member rankMemorable Member
5 years ago

Hi,

 

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

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Nah, this isn't it.

     

    Thanks for the effort though.

    • stevedep's avatar
      stevedep
      Icon for Memorable Member rankMemorable Member
      5 years ago

      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's avatar
        Anonymous
        Not applicable
        5 years ago

        Could you explain the code?

        I am not able to understand it in this context