Forum Discussion
Total Measure - Counting with Unrelated Date Table
- 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.
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.