Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
rjhale
Helper V
Helper V

Total Measure - Counting with Unrelated Date Table

Looking for some help with this measure.  We have data that looks like this: 

COMMITMENT_START_DATECOMMITMENT_END_DATECOMMITMENT_TOTAL_PCTNAMEPERSON_IDCOMMITMENT_DATE_LAST_UPDATEDProjectIDProjectStatus
8/15/20217/14/20226.25Doe, John0055557/12/2021AAJ51781-Open
9/15/20098/31/20138.00Doe, John0055558/3/2012PRJ29MW5-Closed
9/1/20138/31/201710.00Doe, John0055556/22/2016PRJ75YR6-Closed - Archived

 

We want to display a matrix that shows every month what that person's commitment to the project is based on the start and end dates in the first two columns.  We created a date table (this is not related to the commitments table above) and wrote a measure like this: 

 

 

Commitment Total % =
VAR endOfPeriod =
    MAX ( 'Date'[Date] )
VAR startOfPeriod =
    MIN ( 'Date'[Date] )
RETURN
    CALCULATE (
        SUM ( 'COMMITMENTS'[COMMITMENT_TOTAL_PCT] ) / 100,
        'COMMITMENTS'[COMMITMENT_START_DATE] <= endOfPeriod,
        'COMMITMENTS'[COMMITMENT_END_DATE] >= startOfPeriod
    )

 

 

That works, and we get a nice breakdown like this: 

rjhale_0-1659539877829.png

 

Now I need to update the Total Row to count the number of months each person had a commitment.  In the above example, we should be totaling up to 108 (12 months + 48 months + 48 months).  I figured out how to change the grand total row total using a conditional statement with the function HasOneValue, but I'm lost as to how to count the unique year+month combination in the total line. 

 

Any thoughts? 

1 ACCEPTED SOLUTION
rjhale
Helper V
Helper V

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.  

View solution in original post

3 REPLIES 3
rjhale
Helper V
Helper V

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.  

daXtreme
Solution Sage
Solution Sage

// Unique combinations:

SUMMARIZE(
    'Table',
    'Table'[Column1],
    'Table'[Column2],
    ...
    'Dim1'[Col1],
    ...
    'Dim2'[Col1],
    ...
)

// If a dim is connected to a fact table via one-to-many,
// then you can also group by the columns of the dim.

I'm not sure I follow this.  In my post, I mentioned that the data table isn't connected to my commitments table.  I can't think of how I would relate the two tables because I'm just trying to show what someone's commitment is for every month that the project is open.  The percentage doesn't change for the length of the project.  

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors