How to sum a Total within a matrix that uses a calculation for values

[mbigg]

Hi All,

I have a mtrix in power BI. It contains peoples names along the top and client names along the rows. To get the values in the matrix is a calulation function. This calulcation function does not work for the total though. Is there a way to re-configure the formula I have started to get the total to work correctly?

Thanks

[parry2k]

@mbigg yes it is, not sure what you are trying to do there. It is sure your else branch of the statement is not working. If you can put sample data in a pbix file, it will be much easier to get you the solution.

BTW, Total_Row is a measure.

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[parry2k]

@mbigg I guess you are referring to calculation as a measure? What is the expression of that measure? Share the DAX code.

[mbigg]

This is my current DAX:

Total_Row =

Var Revenueperperson = sum('Invoice Summary'[Total_GC])*divide(CALCULATE(sum('Harvest Actual'[Hours])),CALCULATE(sum('Harvest Actual'[Hours]),ALL(People[Full Name])))

Var Revenueperpersonsum = SUMMARIZE('Harvest Actual',"client revenue",Revenueperperson)

Return

if(HASONEVALUE(Clients[Client])&&HASONEVALUE(People[Full Name]),

Revenueperperson,

sumx(Revenueperpersonsum,[client revenue]))

There are no measures in this calulcation. It is essentially trying to make a calulation for the cells and a total down the bottom. I think the issue may be in the summarise portion of the formula.

[Icey · Accepted solution]

Hi @mbigg ,

Try this:

Total_Row =
VAR Revenueperperson =
    SUM ( 'Invoice Summary'[Total_GC] )
        * DIVIDE (
            CALCULATE ( SUM ( 'Harvest Actual'[Hours] ) ),
            CALCULATE ( SUM ( 'Harvest Actual'[Hours] ), ALL ( People[Full Name] ) )
        )
VAR Revenueperpersonsum =
    SUMMARIZE (
        'Harvest Actual',
        Clients[Client],
        People[Full Name],
        "client revenue", Revenueperperson
    )
RETURN
    IF (
        HASONEVALUE ( Clients[Client] ) && HASONEVALUE ( People[Full Name] ),
        Revenueperperson,
        SUMX ( Revenueperpersonsum, [client revenue] )
    )

If the expression above doesn't work, try to create two measures:

Revenueperperson =
    SUM ( 'Invoice Summary'[Total_GC] )
        * DIVIDE (
            CALCULATE ( SUM ( 'Harvest Actual'[Hours] ) ),
            CALCULATE ( SUM ( 'Harvest Actual'[Hours] ), ALL ( People[Full Name] ) )
        )

Revenueperperson with total = 
IF (
        HASONEVALUE ( Clients[Client] ) && HASONEVALUE ( People[Full Name] ),
        [Revenueperperson],
        SUMX ( 'Harvest Actual', [Revenueperperson] )
    )

Best regards

Icey

If this post helps, then consider Accepting it as the solution to help other members find it faster.