Forum Discussion

hama21's avatar
hama21
Icon for Helper I rankHelper I
3 years ago

Difference between date.[month] and date.[year]

Hello everyone,   I have a table in my report where I use two measures that are as follows:   measure c = DIVIDE((SUM('Table[Value A])*12),SUM('Table'[Value B]))   and measure to calculate the ...
Need Help
Tips and Tricks
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
3 years ago

hama21 Well, the reason is that you are taking an average across either 2 rows (Year) or 12 rows (Month). It is also possible that VALUES is returning a blank row and that might also throw some things off. I think that your formula would be much more clear and more flexible if you did not use the auto-time intelligence stuff which has the potential to cause issues as well as use SUMMARIZE or DISTINCT versus VALUES. Seriously, don't use VALUES: https://youtu.be/vOUdKXZpmSc

 

  • hama21's avatar
    hama21
    Icon for Helper I rankHelper I
    3 years ago

    How do you recommend my formula? I'm watching the video right now. Thanks for the answer Greg_Deckler 

    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      3 years ago

      hama21 I would need a better sense of your source data to be 100% sure. Is what you posted a good example of how your source data looks? But, I would personally try to do something along the lines of:

      Average Measure =
  VAR __Table =
    SUMMARIZE(
      ADDCOLUMNS(
        'Table',
        "__Month",MONTH('Table'[Date])
      ),
      [__Month],
      "__Measure",[measure c]
    )
RETURN
  AVERAGEX(__Table,[__Measure])

      A little more verbose because I don't know if you have a Month column already. The advantages of this approach is that you can troubleshoot it by using CONCATENATEX instead of AVERAGEX to inspect your table values within the calculation itself. Also, this method removes any issues that might be caused via table configuration. This form works in a single table data model as well as a star, schema, etc.

      • hama21's avatar
        hama21
        Icon for Helper I rankHelper I
        3 years ago

        Greg_Deckler It's working but I received different value when using .[month] or .[year]. I want to understand why this is happening because the value must be the same