Forum Discussion

rjhale's avatar
rjhale
Icon for Helper V rankHelper V
4 years ago
Solved

Total Measure - Counting with Unrelated Date Table

Looking for some help with this measure.  We have data that looks like this:  COMMITMENT_START_DATE COMMITMENT_END_DATE COMMITMENT_TOTAL_PCT NAME PERSON_ID COMMITMENT_DATE_LAST_UPDATED Proj...
  • rjhale's avatar
    4 years ago

    Ok.  I think I figured this out after a lot of trial and error.  I'm posting my own solution in hopes that it helps someone else down the road.  I started by creating a summarization table that incorporates the measure I mentioned in my first post:

    Commitments by Month = 
       ADDCOLUMNS(Filter(SUMMARIZECOLUMNS(
          'COMMITMENTS'[ProjectID],
          'COMMITMENTS'[NAME],
          'COMMITMENTS'[PERSON_ID],
          'COMMITMENTS'[PROJECTS_KEY],
          'COMMITMENTS'[Active Commitment],
          'COMMITMENTS'[Fund],
          'COMMITMENTS'[ProjectStatus],
          'Date'[Year+Month],
          "Commitment Total %", '_Measures'[Commit Total %],
        ), 'COMMITMENTS'[NAME] <> BLANK()), "Count", 1)

     

    After that, I created a new measure for formatting the total line slightly differently from the individual rows:

    Commitment Total-Formatted =
    IF (
        HASONEVALUE ( 'Commitments by Month'[Year+Month] ),
        SUM ( 'Commitments by Month'[Commitment Total %] ),
        FORMAT ( SUM ( 'Commitments by Month'[Commitment Total %] ), "Fixed" )
    )

     

    The summarization table was the key to getting this to work as expected.