Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Correct SUMMARIZATION in a SUMMARIZE TABLE

Hi there,

I need to achieve something and I know it's possible, only I don't know how and have spent too many days struggling with this. It will take me a bit to explain, so thanks in advance x your patience.

 

I have this table that has many more criteria but the ones that interest me are the ones shown. It's a built-in table (not in the P.Query) with the following dax:

 

 

TONS/PHASE TABLE = // en esta tabla los ratios están bien, cuando se ven por phase
    ADDCOLUMNS( 'Hours'
            ,"PHASE", RELATED('All_ALL'[PHASE])
            ,"ACT_FINISH_DATE", RELATED('All_ALL'[ACT_FINISH_DATE])
)

 

 

The last column is from the next measure:

 

 

ASSIGN_RATIO_BY_PHASE = 
VAR NUM_ = 
Calculate( 
    [ASSIGN_HRS_BY_PHASE],
         FILTER('TONS/PHASE TABLE', 'TONS/PHASE TABLE'[TEAM] = "CCB")
)

RETURN
DIVIDE( NUM_,  [ASSIGN_HRS_BY_PHASE], 0)

 

 

 

When I visualize the data by PROJECT and PHASE the ASSIGN_RATIO_BY_PHASE is exactly the figure that I need.


And since I need to apply it, I decided to SUMMARIZE another table just with the criteria required but, the results gives me different percentages that the one I visualize in the original Table 

The DAX used to SUMMARIZE the table is the following:

 

 

PROJECT/PHASE_RATIO = 
SUMMARIZE( 'TONS/PHASE TABLE'
          , 'TONS/PHASE TABLE'[PROJECT]
          , 'TONS/PHASE TABLE'[PHASE]
          , "ASSIGN_RATIO_BY_PHASE", 
                CALCULATE( [ASSIGN_RATIO_BY_PHASE]
                    , ALLEXCEPT( 'TONS/PHASE TABLE','TONS/PHASE TABLE'[PROJECT], 'TONS/PHASE TABLE'[PHASE] ) ) 
          , "RELEASE_TO_FAB_DATE", 
                CALCULATE( 
                    MAX( 'TONS/PHASE TABLE'[ACT_FINISH_DATE] )
                    , FILTER('TONS/PHASE TABLE', 'TONS/PHASE TABLE'[TASK] = "Release to Fabrication" )
                )
)

 

 

For the "ASSIGN_RATIO_BY_PHASE" column also tried with this option but with the exact same result

 

 

          , "ASSIGN_RATIO_BY_PHASE", 
                AVERAGEX( 
                    SUMMARIZE( 'TONS/PHASE TABLE','TONS/PHASE TABLE'[PROJECT], 'TONS/PHASE TABLE'[PHASE]) 
                    , [ASSIGN_RATIO_BY_PHASE] ) // Tried this to get the assignation ratio for project by Phase but got same result than metric

 

 

 

As I mentioned, I know that what I need is doable, just I can't figure it out so,
Help!

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    This is an example of the data:

     

    PROJECTTASKTEAMPHASEPlanned Hours
    89685Design Check CCBPhase 0143
    89685Prod Detail CCBPhase 0942
    89685Prod Detail CCBPhase 0840
    89685Prod Detail VVDPhase 0935
    89685Prod Detail VVDPhase 0425
    89685Prod Detail VVDPhase 0525
    89685Review CCBPhase 0824
    89685Prod Check TrimCCBPhase 0821
    89685Prod Detail VVDPhase 0120
    89685Prod Detail VVDPhase 0220
    89685Prod Detail VVDPhase 0420
    89685Prod Detail VVDPhase 0520
    89685Prod Check CCBPhase 0116
    89685Prod Check CCBPhase 0216
    89685Prod Check CCBPhase 0316
    89685Prod Check CCBPhase 0416
    89685Prod Check CCBPhase 0516
    89685Prod Detail VVDPhase 0111
    89685Prod Detail VVDPhase 0211
    89685Prod Detail VVDPhase 0311
    89685Prod Detail VVDPhase 0411
    89685Prod Detail VVDPhase 0511
    89685DesignCCBPhase 018
    89685Prod Detail Corrections VVDPhase 038
    89685Drawings ReviewCCBPhase 014
    89685Drawings ReviewCCBPhase 024
    89685Drawings ReviewCCBPhase 044
    89685Drawings ReviewCCBPhase 054
    89685Prod DetailCCBPhase 094
    89685Drawings ReviewCCBPhase 031
    89685Drawings ReviewCCBPhase 090.5
    89685Drawings ReviewCCBPhase 090.5
    89685DrawingsCCBPhase 090.5
    89685DrawingsCCBPhase 010.5
    89685DrawingsCCBPhase 020.5
    89685DrawingsCCBPhase 030.5
    89685DrawingsCCBPhase 040.5
    89685DrawingsCCBPhase 050.5
    89685Drawings ReviewCCBPhase 010.06
    89685Drawings ReviewCCBPhase 020.06
    89685Drawings ReviewCCBPhase 030.06
    89685Drawings ReviewCCBPhase 040.06
    89685Drawings ReviewCCBPhase 050.06
    89685Design Release to DraftingCCBPhase 010
    89685IFC Drawings MailedCCBPhase 010
    89685IFC Drawings MailedCCBPhase 020
    89685IFC Drawings MailedCCBPhase 030
    89685IFC Drawings MailedCCBPhase 040
    89685IFC Drawings MailedCCBPhase 050
    89685Release to FabricationCCBPhase 010
    89685Release to FabricationCCBPhase 020
    89685Release to FabricationCCBPhase 030
    89685Release to FabricationCCBPhase 040
    89685Release to FabricationCCBPhase 050
    89685Release to FabricationCCBPhase 080
    89685Release to FabricationCCBPhase 090
    72589Design CheckCCBPhase 0150
    72589Prod DetailCCBPhase 0932
    72589Prod DetailCCBPhase 0880
    72589Prod DetailVVDPhase 0953
    72589Prod DetailVVDPhase 0452
    72589Prod DetailVVDPhase 0552
    72589ReviewdwgsCCBPhase 0842
    72589Prod Check Trim CCBPhase 0812
    72589Prod Detail Corrections VVDPhase 0120
    72589Prod Detail Corrections VVDPhase 0210
    72589Prod Detail Corrections VVDPhase 0435
    72589Prod Detail Corrections VVDPhase 0525
    72589Prod CheckCCBPhase 0116
    72589Prod CheckCCBPhase 0235
    72589Prod CheckCCBPhase 0345
    72589Prod CheckCCBPhase 0452
    72589Prod CheckCCBPhase 0583
    72589Prod Detail VVDPhase 0145
    72589Prod DetailVVDPhase 0212
    72589Prod DetailVVDPhase 0310
    72589Prod DetailVVDPhase 0498
    72589Prod DetailVVDPhase 0583
    72589Design CCBPhase 0188
    72589Prod Detail CorrectionsVVDPhase 035
    72589Drawings ReviewCCBPhase 017
    72589Drawings ReviewCCBPhase 024
    72589Drawings ReviewCCBPhase 042
    72589Drawings ReviewCCBPhase 0510
    72589Prod DetailCCBPhase 093
    72589Drawings ReviewCCBPhase 0310
    72589Drawings ReviewCCBPhase 090.5
    72589Drawings ReviewCCBPhase 090.5
    72589MailDrawings CCBPhase 090.5
    72589MailDrawings CCBPhase 010.5
    72589MailDrawings CCBPhase 020.5
    72589MailDrawings CCBPhase 030.5
    72589MailDrawings CCBPhase 040.5
    72589MailDrawings CCBPhase 050.5
    72589Sealing VVDPhase 011
    72589Sealing VVDPhase 021
    72589Sealing VVDPhase 031
    72589Sealing CCBPhase 041
    72589Sealing CCBPhase 051
    72589Design Release to FabCCBPhase 010
    72589IFC Drawings MailedCCBPhase 010
    72589IFC Drawings MailedCCBPhase 020
    72589IFC Drawings MailedCCBPhase 030
    72589IFC Drawings MailedCCBPhase 040
    72589IFC Drawings MailedCCBPhase 050
    72589Release to FabricationCCBPhase 010
    72589Release to FabricationCCBPhase 020
    72589Release to FabricationCCBPhase 030
    72589Release to FabricationVVDPhase 040
    72589Release to FabricationVVDPhase 050
    72589Release to FabricationVVDPhase 080
    72589Release to FabricationVVDPhase 090
         
         
    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion

      Anonymous What about this?

      Table 2 = 
          VAR __Table = 
              ADDCOLUMNS(
                  SUMMARIZE('Table', [PROJECT], [PHASE], "Hours", SUM('Table'[Planned Hours]), "CCB", SUMX(FILTER('Table',[TEAM] = "CCB"),[Planned Hours])),
                  "Ratio", DIVIDE([CCB], [Hours])
              )
      RETURN
          __Table
      • Anonymous's avatar
        Anonymous
        Not applicable

        Hi Greg_Deckler ,

         

        I'll keep in mind your suggestion regarding the use of CALCULATE.
        On the other hand I tried this new alternative and the result is the same.

        I wonder if the fact that this table grouping the blank (No PHASE assigned) have something to do with the difference in the results.

         

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion

    Anonymous Try:

    PROJECT/PHASE_RATIO = 
    SUMMARIZE( 'TONS/PHASE TABLE'
              , 'TONS/PHASE TABLE'[PROJECT]
              , 'TONS/PHASE TABLE'[PHASE]
              , "ASSIGN_RATIO_BY_PHASE", 
                    [ASSIGN_RATIO_BY_PHASE],
              , "RELEASE_TO_FAB_DATE", 
                    CALCULATE( 
                        MAX( 'TONS/PHASE TABLE'[ACT_FINISH_DATE] )
                        , FILTER('TONS/PHASE TABLE', 'TONS/PHASE TABLE'[TASK] = "Release to Fabrication" )
                    )
    )
    • Anonymous's avatar
      Anonymous
      Not applicable

      Hi Greg_Deckler thank you,

      I did it but the result is the same. The percentages remain the same in the SUMMARIZE table!
      Any idea!!!