Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

Best Way to Summarize Data

Hello all,

 

I need to summarize the data in the screen shot, it needs to show the amount of PAID_HOURS and MONETARY_AMOUNT grouped by month by FinHrRollupName.

 

Example:

 

FinHrRollupNameJAN PAID HOURSJAN MONETARY AMOUNTFEB PAID HOURSFEB MONETARY AMOUNT

GALVESTON_ME_CENTER 
{SUM OF ALL JAN PAID HOURS}{SUM OF ALL JAN MONETARY AMOUNT}{SUM OF ALL FEB PAID HOURS}{SUM OF ALL FEB MONETARY AMOUNT}
ANGLETON_DANBURY_HOSP{SUM OF ALL JAN PAID HOURS}{SUM OF ALL JAN MONETARY AMOUNT}{SUM OF ALL FEB PAID HOURS}{SUM OF ALL FEB MONETARY AMOUNT}

 

What would be the best way to do this?

Group data.PNG

 

@edhans 

6 REPLIES 6
v-frfei-msft
Community Support
Community Support

Hi @Anonymous ,

 

We can create a measure like that to work on it.

 

Measure =
VAR name =
    MAX ( table[FinHrRollupName] )
RETURN
    CALCULATE (
        SUM ( table[value] ),
        FILTER ( ALLSELECTED ( table ), table[FinHrRollupName] = name )
    )

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Anonymous
Not applicable

Can you take a look at this file and see if that will work

 

https://www.dropbox.com/s/i33ogvhxifczid6/PBLs%20v2.pbix?dl=0

 

@v-frfei-msft 

edhans
Super User
Super User

If this is in Power QUery, click Group By, then the Advanced radio button and you can group using the SUM function at the bottom of the dialog box.

 

If you have to have this in DAX, I think SUMMARIZECOLUMNS() is the function to use, but I'd need data to play with to show you the measure.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

I think it would have to be done in DAX

 

Here is the link

 

https://www.dropbox.com/s/dnbucapacowwwdb/PBLs.pbix?dl=0

 

@edhans 

@Anonymous I spent half an hour on this at lunch and I cannot figure the model out in that timeframe. There is no "table" you are wanting to summarize. There are at least three, and there is no date table that would be needed to accomplish this. There was no Model View for how this visual was created. There was just the default "ALL TABLES" page with several dozen tables.

 

I think this is a fairly simple summarization, because SUMMARIZECOLUMNS() is fundamentally a basic concept (group by these columns, then do math on these columns as a summary), but trying to figure out how all the relationships work is a bit of detective work just to get to the example. And the summary calcs will be using RELATED, FILTER, and SUMX.

 

If you want to provide a simplifed dataset as an example, I'd happily work on that. Otherwise, this article can help you work through it as you probably grasp how this model works in your head pretty well.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.