Forum Discussion

CraigSchulz's avatar
CraigSchulz
Icon for Helper II rankHelper II
8 years ago

Measure Needs Different Denominator for One Account

I have the following measure that calculates the average number of cars parked by noon on the same day of the week as the current day of the week. On a Friday, for any given account it will give me the average number for Fridays since the beginning of the year.

 

AvgParkedByNoon = CALCULATE(
DIVIDE(SUM(SMSYTD[ParkedByNoon]),DISTINCTCOUNT('Calendar'[Date])),
'Calendar'[IsHoliday]=FALSE(),
FILTER('Calendar','Calendar'[Day of Week] = [DoWtoday]))

 

This is working well, but one of the accounts is fairly new and still growing, so it doesn't make sense to look all the way to the beginning of the year for the denominator because there was no data early in the year. In fact I would like to only use the relevant days in July of this year for this particular account.

Can anyone help me with the code to do this?

 

Here is a link to a sample data file.

https://drive.google.com/file/d/1M2HOFq43UB7qduMqZw_EcBnQK_wecrN-/view?usp=sharing

 

- Craig

Need Help

4 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    8 years ago

    Hi CraigSchulz

    Try this measure

    Assume the particular account is "a", 

    Month=MONTH('Calendar'[Date])

    AvgParkedByNoon = IF([Area]="a",
    CALCULATE(
    DIVIDE(SUM(SMSYTD[ParkedByNoon]),DISTINCTCOUNT('Calendar'[Date])),
    'Calendar'[IsHoliday]=FALSE(),
    FILTER('Calendar','Calendar'[Day of Week] = [DoWtoday] &&[Month]=7)),
    CALCULATE(
DIVIDE(SUM(SMSYTD[ParkedByNoon]),DISTINCTCOUNT('Calendar'[Date])),
'Calendar'[IsHoliday]=FALSE(),
FILTER('Calendar','Calendar'[Day of Week] = [DoWtoday]))

     

    Best Regards

    Maggie

    • CraigSchulz's avatar
      CraigSchulz
      Icon for Helper II rankHelper II
      8 years ago

      This measure did work, once I changed the first lF to IF(VALUES(AccountAreaMaster[AcctAreaID]) = "130 - Hope Tower",

       

      It broke my visual however, giving me the following error message:

      Error Message:

      MdxScript(Model) (7, 5) Calculation error in measure 'SMSYTD'[AvgParkedByNoon]: A table of multiple values was supplied where a single value was expected.

       

      Perhaps this is because the visual starts out comparing Accounts ("average parked by noon" with "parked by noon today"), and then you can drill down to show AccountAreas with the same comparison. AcctArea "130 - Hope Tower" is one of two areas under AccountID "130", the other being AcctArea "130 - Main".

      Is this the source of the error?

      If not, what is?

      Any thoughts on a solution?

       

      - Craig

       

      • v-juanli-msft's avatar
        v-juanli-msft
        Icon for Community Support rankCommunity Support
        8 years ago

        Hi CraigSchulz

        VALUES function would return a table not a column or a scalar value, but the right of the "=" is a text value.

         

        Best Regards

        Maggie