Forum Discussion

DPCCGF's avatar
DPCCGF
Icon for Helper IV rankHelper IV
2 years ago

Matrix, Sum and Averages

I have a table that has rows containing Client, Job, Staff, Date, Hours, Job Value.  The Job value is set as an Average summarization.  When I create a matrix what I would like is do not see the Sum of Averages at the subtotal levels but instead the Actual Sum.  Then potentially hide the Average Job Value (3500, 2000, 1000) at the lowest level.  Need this all in one column.  Do not need to even see the average job value at the Staff level either.  I cannot sum the average Job Value at the Client or Job 1 or even the Staff level as I get the sum of the lowest level, the Date.  If I use the average it all works well except for the Client level.   Any help would be appreciated.

 

Client  6500  (Sum of Job 1, Job 2 and Job 3 - not the average)

 Job 1  3500 (here the average is fine)

   Staff 1  3500 (hide)

      Date 1 3500 (hide)

      Date 2 3500 (hide)

   Staff 2  3500 (hide)

      Date 1 3500 (hide)

      Date 2 3500 (hide)

      Date 3 3500 (hide)

 Job 2  2000 (here the average is fine)

    Staff 1 2000 (hide)

      Date 1 2000 (hide)

   Staff 2 2000 (hide)

      Date 1 2000 (hide)

 Job 3 2000 (here the average is fine)

   Staff 1 1000 (hide)

      Date 1 1000 (hide)

Need Help

19 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    2 years ago

    here is a workaround for you

     

    Measure = if(not(HASONEVALUE('Table'[staff]))&&not(HASONEVALUE('Table'[date])),sum('Table'[value]),blank())
     
    you need to mofify the sum part to your own DAX
     
    then show items with no data
     
     
    • DPCCGF's avatar
      DPCCGF
      Icon for Helper IV rankHelper IV
      2 years ago

      Thank you.......still having an issue although it did work on two of my thousands of clients.  Here is an example of one of them.  You see that the measure is showing blanks in all Clients except for two of them and in those two it works.  So I think it is the Client amount that needs fixed.  The Job "sum" should be fine.  This is my formula:

       

      Measure = if(not(HASONEVALUE('FACT Work in Progress Invoices (PE)'[Staff Name]))&&(HASONEVALUE('FACT Work in Progress Invoices (PE)'[WIP Date])),sum('FACT Work in Progress Invoices (PE)'[Job.Job Value]),BLANK())

       

       

    • DPCCGF's avatar
      DPCCGF
      Icon for Helper IV rankHelper IV
      2 years ago

      See my response below, I did find my issue in the formula.  However, using the example below I come up with a SUM at the client level of $45,501 (s/b $3501), and then at the Job Level I get the Sum of all the amounts at the lowest level.  So for the first job it is 45,000 (s/b $3500) and second job is $0 (s/b $1).  Thank you for your assistance.