Best Way to Summarize Data

[Anonymous]

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:

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}

What would be the best way to do this?

@edhans 

[v-frfei-msft]

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 )
    )

[Anonymous]

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]

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.

[Anonymous]

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 

[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.

[Anonymous]

Here is a simplified model

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

@edhans