cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Anonymous
Not applicable

## 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
,"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!

1 ACCEPTED SOLUTION
Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
7 REPLIES 7
Super User

@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" )
)
)``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
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!!!

Anonymous
Not 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
Super User

``````Table 2 =
VAR __Table =
SUMMARIZE('Table', [PROJECT], [PHASE], "Hours", SUM('Table'[Planned Hours]), "CCB", SUMX(FILTER('Table',[TEAM] = "CCB"),[Planned Hours])),
"Ratio", DIVIDE([CCB], [Hours])
)
RETURN
__Table``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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.

Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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!

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors