Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

how do I sum data by week from different tables

Hi all,

 

I have a visual table that I made from different columns from different tables.

and I have a bar graph for the same set of data.

currently, the data is showing daily value. I have a time slicer that I set it week #.

the table would show daily value for the weeks I selected from the slicer.

I would like the table and the graph to show the weekly total.

How can I do that? 

 

 

Need Help

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    I would like the BI to mimic the excel screenshot I have below.

    to show the values by weeks, or by month, instead of daily.

     

    • v-xicai's avatar
      v-xicai
      Icon for Community Support rankCommunity Support
      7 years ago

      Hi Anonymous ,

       

      You can create column named Week to calculate the week number in table KPIDates.

       

      Week = WEEKNUM('DateKey'[Date])

       

      Then, create measure Sum Per Week like DAX below..

       

      Sum Per Week = CALCULATE(SUM('DataTable'[Your sum field]),FILTER(ALLSELECTED('DataTable'),'DataTable'[Week]=MAX('DataTable'[Week])))

       

      Best Regards,

      Amy

       

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi Amy,

         

        I tried the WEEKNUM function, but it was telling me it is not allowed as part of calculated column DAX expressions on DirectQuery models.

        is there any workaround while keeping it as Direct Query?

        Also, the "E on dock" and "W on dock"

        "DF MMR" and "RF MMR" are measures I created, both from different tables.

  • v-xicai's avatar
    v-xicai
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi  Anonymous ,
     
    Does that make sense? If so, kindly mark my answer as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

     

    Best regards
    Amy

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Amy,

       

      I was thinking suming the On Dock move and the Off Dock moves together,

      but they are 2 different columns in the 2 different tables. 

      should I create a measure in each table first, then sum them together?