Measure does not return correct subtotals

[niculeica]

Hi, community,

Any idea why a measure would not sum up (at all), in Grand total row? Below is the formula.

_Actual RR =

VAR CUR_RevType =

MAX('(Power BI) - Projects & Tasks - Mavenlink'[Revenue Type])

RETURN

IF(CUR_RevType="Fixed Price",[_Actual RR **bleep**]-[_Previous RR **bleep**],

IF(CUR_RevType="Fixed Monthly Fee",[_Actual RR **bleep**],

IF(CUR_RevType="T&M",[_Billing R]*[_Hours Approved])))

It calculates a first Subtotal for a client that has values for a specified timeframe, then there's a second client with no values. So it does not sum up the first subtotal plus the second (which is null).

[Anonymous · Accepted solution]

Hi @niculeica ,

If I understand correctly, the issue is that you couldn’t get the correct subtotals. Please try the following methods and check if they can solve your problem:

1.Try to use the sumx function to sum up. Modify the formula and enter the following DAX formula.

Actual RR = 
VAR CUR_RevType = MAX('(Power BI) - Projects & Tasks - Mavenlink'[Revenue Type])
RETURN
IF(CUR_RevType="Fixed Price",[_Actual RR **bleep**]-[_Previous RR **bleep**],
IF(CUR_RevType="Fixed Monthly Fee",[_Actual RR **bleep**],
IF(CUR_RevType="T&M",SUMX(VALUES(Client[Client Name]),[_Billing R]*[_Hours Approved]))))

2.Select the matrix visual. Navigate to the Format section and expand the totals option.

If the above ones can’t help you get it working, could you please provide more raw data(exclude sensitive data) with Text format to make a deep troubleshooting? It would be helpful to find out the solution.

Best Regards,

Wisdom Wu