sum by group

[muelledg]

I have a column, hours, that I want to sum by another column, Summary. See below for example.

Hours	Summary
7	Architect
8	Sales
7	Sales
9	Architect
6	Architect
4	Engineer

Initially, I wrote the following DAX expression for a new measure.

HERTP = Calculate(SUM(tablename[hours]),ALLEXCEPT(tablename[Summary]),tablename[Index] <= Earlier(tablename[Index])). 

For some reason, the file is in an endless "working on it" loop. It hasn't stopped yet. Is this the correct DAX statement? If not, please provide a revised statement. Thanks! 

[v-xianjtan-msft]

Hi @muelledg 

Agree with HamedM1125.

Please provide sample data that covers your problem.

I have performed a simple test using data based on currently known information. There is no need to use measures to achieve the result you want.

Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[muelledg]

Hello. The sample data is below. Note that the data model looks like the figure below. The tables are linked on a unique ID called recid. Summary is a text variable that would not be used. The recid is numeric and is unique. v_rpt_Project is tablename and v_api_collection_time is tablename2. The PM_Project table is a bridge table. 

[v-xianjtan-msft · Accepted solution]

Hi @muelledg 

Perhaps the request was not clear enough in my previous reply.

We need to know what data is being used in the measure you are having problems with (not limited to relationships between tables). Based on the screenshot, we are not sure [summary] [index] [hours] correspond to which fields in your actual data.
Please share your pbix file if you don't mind. This is the quickest way for us to understand your problem. Alternatively, please provide the dummy data in tabular form that maintains the same data structure as the three actual data tables.

Thank you for your understanding.

How to Get Your Question Answered Quickly - Microsoft Fabric Community

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[HamedM1125]

@muelledg 

Not sure how you've considered the Index in your data. Please share details!
I just tried assuiming the data, it is better if you can share sample data and the measures you tried. 

[muelledg]

Thank you Hamed. I don't think I provided enough information. As a result, that DAX statement does not quite provide me what I want.

So, oddly that works in one table visual, but not the other. In one visual, it displays a column that sums exactly as described (by Summary). However, in another table it displays the sum of all of the values. For example,

Table Visual 1 - This table uses only columns from the tablename table in the data view.

Summary	Hours
Architect	22
Sales	15
Engineer	4
Total	41

Table Visual 2 - This table uses only columns from a different table in the data view. Call it tablename2. When I enter the measure in this table it displays as follows. Selecting "Don't Summarize" results in an expanded table that lists every data element for the Hours column which is not what I want. 

Summary	Hours(tablename)	LastWeekHours(tablename2)
Architect	41	7
Sales	41	8
Engineer	41	9

What I want is this:

Summary	Hours(tablename)	LastWeekHours(tablename2)
Architect	22	7
Sales	15	8
Engineer	4	9

[FreemanZ]

hi @muelledg ,

try to write a calculated column like:

HERTP =

SUMX(

    FILTER(

        tablename,

        tablename[Summary]=EARLIER(tablename[Summary])

            &&tablename[Index]<EARLIER(tablename[Index])

    ),

    tablename[hours]

)

[HamedM1125]

@muelledg 

Try this Dax!

HERTP =
SUMX(
VALUES(tablename[Summary]),
CALCULATE(SUM(tablename[Hours]))
)