Best Way to Summarize Data

Question

Hello all,&nbsp;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&nbsp;FinHrRollupName.&nbsp;Example:&nbsp;FinHrRollupNameJAN PAID HOURSJAN MONETARY AMOUNTFEB PAID HOURSFEB MONETARY AMOUNTGALVESTON_ME_CENTER&nbsp;{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}&nbsp;What would be the best way to do this?&nbsp;edhans&nbsp;

edhans · Answer

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.
&nbsp;
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.

edhans · Answer

Anonymous&nbsp;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.
&nbsp;
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.
&nbsp;
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.

anonymous · Answer

I think it would have to be done in DAX&nbsp;Here is the link&nbsp;https://www.dropbox.com/s/dnbucapacowwwdb/PBLs.pbix?dl=0&nbsp;edhans&nbsp;

v-frfei-msft · Answer

Hi&nbsp;Anonymous&nbsp;,
&nbsp;
We can create a measure like that to work on it.
&nbsp;
Measure =
VAR name =
    MAX ( table[FinHrRollupName] )
RETURN
    CALCULATE (
        SUM ( table[value] ),
        FILTER ( ALLSELECTED ( table ), table[FinHrRollupName] = name )
    )

&nbsp;

anonymous · Answer

Here is a simplified model&nbsp;&nbsp;https://www.dropbox.com/s/i33ogvhxifczid6/PBLs%20v2.pbix?dl=0&nbsp;&nbsp;edhans&nbsp;

Forum Discussion

Best Way to Summarize Data

6 Replies

Related content

Parameter in summarize

Don't Summarize....still summarizes...?

RLS Data summarized and detail

Export Summarized Data on button

GroupBY and summarize

FinHrRollupName	JAN PAID HOURS	JAN MONETARY AMOUNT	FEB PAID HOURS	FEB 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}