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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
mbigg
Frequent Visitor

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

mbigg_0-1608768061353.png

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

 

1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

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.

View solution in original post

5 REPLIES 5
parry2k
Super User
Super User

@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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Ashish_Mathur
Super User
Super User

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
parry2k
Super User
Super User

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

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

mbigg
Frequent Visitor

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
Community Support
Community Support

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors