Correct SUMMARIZATION in a SUMMARIZE TABLE

[Anonymous]

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!

[Greg_Deckler]

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

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!!!

[Anonymous]

This is an example of the data:

PROJECT	TASK	TEAM	PHASE	Planned Hours
89685	Design Check	CCB	Phase 01	43
89685	Prod Detail	CCB	Phase 09	42
89685	Prod Detail	CCB	Phase 08	40
89685	Prod Detail	VVD	Phase 09	35
89685	Prod Detail	VVD	Phase 04	25
89685	Prod Detail	VVD	Phase 05	25
89685	Review	CCB	Phase 08	24
89685	Prod Check Trim	CCB	Phase 08	21
89685	Prod Detail	VVD	Phase 01	20
89685	Prod Detail	VVD	Phase 02	20
89685	Prod Detail	VVD	Phase 04	20
89685	Prod Detail	VVD	Phase 05	20
89685	Prod Check	CCB	Phase 01	16
89685	Prod Check	CCB	Phase 02	16
89685	Prod Check	CCB	Phase 03	16
89685	Prod Check	CCB	Phase 04	16
89685	Prod Check	CCB	Phase 05	16
89685	Prod Detail	VVD	Phase 01	11
89685	Prod Detail	VVD	Phase 02	11
89685	Prod Detail	VVD	Phase 03	11
89685	Prod Detail	VVD	Phase 04	11
89685	Prod Detail	VVD	Phase 05	11
89685	Design	CCB	Phase 01	8
89685	Prod Detail Corrections	VVD	Phase 03	8
89685	Drawings Review	CCB	Phase 01	4
89685	Drawings Review	CCB	Phase 02	4
89685	Drawings Review	CCB	Phase 04	4
89685	Drawings Review	CCB	Phase 05	4
89685	Prod Detail	CCB	Phase 09	4
89685	Drawings Review	CCB	Phase 03	1
89685	Drawings Review	CCB	Phase 09	0.5
89685	Drawings Review	CCB	Phase 09	0.5
89685	Drawings	CCB	Phase 09	0.5
89685	Drawings	CCB	Phase 01	0.5
89685	Drawings	CCB	Phase 02	0.5
89685	Drawings	CCB	Phase 03	0.5
89685	Drawings	CCB	Phase 04	0.5
89685	Drawings	CCB	Phase 05	0.5
89685	Drawings Review	CCB	Phase 01	0.06
89685	Drawings Review	CCB	Phase 02	0.06
89685	Drawings Review	CCB	Phase 03	0.06
89685	Drawings Review	CCB	Phase 04	0.06
89685	Drawings Review	CCB	Phase 05	0.06
89685	Design Release to Drafting	CCB	Phase 01	0
89685	IFC Drawings Mailed	CCB	Phase 01	0
89685	IFC Drawings Mailed	CCB	Phase 02	0
89685	IFC Drawings Mailed	CCB	Phase 03	0
89685	IFC Drawings Mailed	CCB	Phase 04	0
89685	IFC Drawings Mailed	CCB	Phase 05	0
89685	Release to Fabrication	CCB	Phase 01	0
89685	Release to Fabrication	CCB	Phase 02	0
89685	Release to Fabrication	CCB	Phase 03	0
89685	Release to Fabrication	CCB	Phase 04	0
89685	Release to Fabrication	CCB	Phase 05	0
89685	Release to Fabrication	CCB	Phase 08	0
89685	Release to Fabrication	CCB	Phase 09	0
72589	Design Check	CCB	Phase 01	50
72589	Prod Detail	CCB	Phase 09	32
72589	Prod Detail	CCB	Phase 08	80
72589	Prod Detail	VVD	Phase 09	53
72589	Prod Detail	VVD	Phase 04	52
72589	Prod Detail	VVD	Phase 05	52
72589	Reviewdwgs	CCB	Phase 08	42
72589	Prod Check Trim	CCB	Phase 08	12
72589	Prod Detail Corrections	VVD	Phase 01	20
72589	Prod Detail Corrections	VVD	Phase 02	10
72589	Prod Detail Corrections	VVD	Phase 04	35
72589	Prod Detail Corrections	VVD	Phase 05	25
72589	Prod Check	CCB	Phase 01	16
72589	Prod Check	CCB	Phase 02	35
72589	Prod Check	CCB	Phase 03	45
72589	Prod Check	CCB	Phase 04	52
72589	Prod Check	CCB	Phase 05	83
72589	Prod Detail	VVD	Phase 01	45
72589	Prod Detail	VVD	Phase 02	12
72589	Prod Detail	VVD	Phase 03	10
72589	Prod Detail	VVD	Phase 04	98
72589	Prod Detail	VVD	Phase 05	83
72589	Design	CCB	Phase 01	88
72589	Prod Detail Corrections	VVD	Phase 03	5
72589	Drawings Review	CCB	Phase 01	7
72589	Drawings Review	CCB	Phase 02	4
72589	Drawings Review	CCB	Phase 04	2
72589	Drawings Review	CCB	Phase 05	10
72589	Prod Detail	CCB	Phase 09	3
72589	Drawings Review	CCB	Phase 03	10
72589	Drawings Review	CCB	Phase 09	0.5
72589	Drawings Review	CCB	Phase 09	0.5
72589	MailDrawings	CCB	Phase 09	0.5
72589	MailDrawings	CCB	Phase 01	0.5
72589	MailDrawings	CCB	Phase 02	0.5
72589	MailDrawings	CCB	Phase 03	0.5
72589	MailDrawings	CCB	Phase 04	0.5
72589	MailDrawings	CCB	Phase 05	0.5
72589	Sealing	VVD	Phase 01	1
72589	Sealing	VVD	Phase 02	1
72589	Sealing	VVD	Phase 03	1
72589	Sealing	CCB	Phase 04	1
72589	Sealing	CCB	Phase 05	1
72589	Design Release to Fab	CCB	Phase 01	0
72589	IFC Drawings Mailed	CCB	Phase 01	0
72589	IFC Drawings Mailed	CCB	Phase 02	0
72589	IFC Drawings Mailed	CCB	Phase 03	0
72589	IFC Drawings Mailed	CCB	Phase 04	0
72589	IFC Drawings Mailed	CCB	Phase 05	0
72589	Release to Fabrication	CCB	Phase 01	0
72589	Release to Fabrication	CCB	Phase 02	0
72589	Release to Fabrication	CCB	Phase 03	0
72589	Release to Fabrication	VVD	Phase 04	0
72589	Release to Fabrication	VVD	Phase 05	0
72589	Release to Fabrication	VVD	Phase 08	0
72589	Release to Fabrication	VVD	Phase 09	0

[Greg_Deckler]

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

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 · Accepted solution]

@Anonymous Maybe because in this PBIX I get identical results in the summarized table and the table visual. See PBIX below.

[Anonymous]

Hi @Greg_Deckler 

It took me a while to get back on track and review the option. It did provide the same results, so I decided go back 1 step and summarize the original table removing all the unnecesary criterias, since one of those was affecting the outcome. After that any of the provide options did gives out with the correct assignation ratio so, thanks for the help!