Forum Discussion
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!
Anonymous Maybe because in this PBIX I get identical results in the summarized table and the table visual. See PBIX below.
7 Replies
- AnonymousNot applicable
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_DecklerCommunity 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- AnonymousNot 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_DecklerCommunity 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" ) ) )- AnonymousNot 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!!!