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 ...
stevedep's avatar
stevedep
Memorable 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
    Memorable 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

      • stevedep's avatar
        stevedep
        Memorable Member
        5 years ago

        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?