Summarizing and Grouping Data

[markefrody]

Hi,

I'm having some difficulty in grouping and summarizing the data in Power BI as per link below. Appreciate if you can give me assistance in processing the data as per steps indicated.

Link

Thanks and best regards,

Mark

[MFelix · Accepted solution]

Hi @markefrody,

You don't need to make any new table to make this summarization, when possible and as a best practice you should create measures instead of columns or new tables.

Create the following 4 measures:

SPHA's_Filled = MAX(SPHA[SPHA's Filled])

SPHAS_FILLED_OFFICE = SUMX(VALUES(SPHA[Job: Name]);SPHA[SPHA's_Filled])


SPHA needed to fulfill = SUM(SPHA[Ticket Man Hours Total]) / 40


SPHA left to fulfill = SPHA[SPHA needed to fulfill] - SPHA[SPHAS_FILLED_OFFICE]

The first one is just an auxiliary measure the other 3 is the ones you should use on your visuals, as you can see below the second measure works with all the Job:name but also without them making the calculations you need to make the grouping. You can use the measures in tables or in other types of visuals.

In attach you can find the PBIX

Regards,

MFelix

[markefrody]

Thanks @MFelix! Your solution worked! Kudos to you Sir! 🙂

[MFelix]

Hi @markefrody,

there is no file in the link you provided.

Regards,

MFelix

[markefrody]

Hi @MFelix,

Please try this:

Link

[Anonymous]

Hello,

Goto to Data pane  and 

Click Edit Queries

Select the query you want to aggreagte

Click Group By

Let me know how this works..

[Anonymous]

The above solution will give you Group by "Job Name "

For Group by "Office"

We need to Duplicate this table and later do a group by 

for duplicate this table  Click on Get data-->blank query --> in formula bar write "= Table Name " this will duplicate

Once you have duplicated do a group by as shown in above step with the column as "Office".

Drag  this field into visualisation

Thanks,

Aditya 

[markefrody]

Thanks @Anonymous for the suggestion. Have a nice day ahead.