Get individual values from table1 where table1 column = table 2 column then sum

[AAMW01]

I have this table:

Rows: Overhead Types

Columns: Fiscal Year

Values: Overhead Cost (from database)
Budget Values (from excel)

Each link to the calendar table. 

The values from the database are per row (transaction). 

The values from the excel are per month & based on Type like sales output, order input, overheads etc..)

My calculation to get that Budget values column is:

Overhead Budget Calc = CALCULATE(SUM('BudgetData21-22'[Value]), ALL('BudgetData21-22'[Value]), FILTER('BudgetData21-22', 'BudgetData21-22'[GroupType] = MAX('cwDashOverHeads-KPI10'[OverheadType])))

I have a filter on the visual that makes sure the budget "KPI" type is Overhead and then it makes sure that the "group" type (individual categories for each kpi type) is under the right kpi type.

Basically I am calculating the sum of the budget excel spreadsheet where the group type (text value equating to each overhead type) equals OverheadType in the database column.
Which gets  the right row values but Total value gets the last value in the rows.

Here is a screenshot of the table

As you can see it performs the calculation based on row correctly, however, gets the max value (last row's value) which I understand makes sense but I cannot figure out how to change it so I get the values in the rows where each group type is the same.

I have tried MAXX and SUMX but may not have been using them correctly in this case.

Any help greatly appreciated.

[AAMW01 · Accepted solution]

I have fixed my own issue. 

The problem was for some reason the query is using Max / Min from the string comparison cells and using that to calculate total value . Not sure why it does that because I used SUM (value) and my filter used MAX(group type) comparison.

I changed the first measure to this:

Overhead Budget Calc = 
CALCULATE(
    SUM('BudgetData21-22'[Value]), 
    FILTER('BudgetData21-22', 'BudgetData21-22'[GroupType] = SELECTEDVALUE('cwDashOverHeads-KPI10'[OverheadType]))
    )

This made the total blank which I then used an if else statement to do a conditional calculation.

total sum OH = 
IF(ISBLANK(CALCULATE('cwDashOverHeads-KPI10'[Overhead Budget Calc])), CALCULATE(SUM('BudgetData21-22'[Value])), [Overhead Budget Calc])

Works perfectly. 

Thanks to those who tried to help.

[Greg_Deckler]

@AAMW01 This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

You will need to SUMX across a SUMMARIZE of your data just as you are summarizing the data in the visual, by Fiscal Year Comparison Type.

[AAMW01]

@Greg_Deckler I have tried using SUMMARIZE but I assume I am not using it correctly.

My first query remains the same but this is my summarized query:

VAR __table = SUMMARIZE('BudgetData','BudgetData21-22'[GroupType],"__value", 'cwDashOverHeads-KPI10'[Overhead Budget Calc])
RETURN
IF(HASONEVALUE('BudgetData21-22'[GroupType]), 'cwDashOverHeads-KPI10'[Overhead Budget Calc], SUMX(__table,[__value]))

but I get similar results

[v-janeyg-msft]

Hi, @AAMW01 

You can try(use sumx and summarize() function):

measure =
SUMX (
    SUMMARIZE (
        BudgetData21 - 22,
        [Value],
        [GroupType],
        [Columns that need to be used in context],
        "a",
            CALCULATE (
                SUM ( 'BudgetData21-22'[Value] ),
                ALL ( 'BudgetData21-22'[Value] ),
                FILTER (
                    'BudgetData21-22',
                    'BudgetData21-22'[GroupType] = MAX ( 'cwDashOverHeads-KPI10'[OverheadType] )
                )
            )
    ),
    [a]
)

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.

Best Regards,

Community Support Team _ Janey

[AAMW01]

@v-janeyg-msft 

Thanks for the reply,

Unfortunately I get the same issue.

I don't know why the function is getting the MAX in value yet I am declaring SUM and filtering by a string's row value = max string value in a row (i.e. when both rows in each table each same group type).

I added the updated function you wrote however still get this result

As you can see it gets the latest rows value as the total and also leaves out tool rooms value.

Any ideas why?

Kind regards

[v-janeyg-msft]

Hello @AAMW01 

Any updates?

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If problem still persists, pls let me know.

Best Regards,

Community Support Team _ Janey

[v-janeyg-msft]

Hi, @AAMW01 

First of all, I don’t know what your data is. So I can only modify your existing measure. If your measure is correct, then the 'total' can be correct by the method of sumx(summarize()).

As for why total doesn't show sum but max value, this is also related to the measure you wrote, because the context of total is different from the context of the row with data on it. It can only be calculated based on the measure you write, total never only represents sum, it can represent any value as long as you want.

You can also judge whether the code is correct or not. You can copy the summrazie() code part in the form of new table in the desktop to check whether this virtual table meets your needs.

If you still can't get your desired result, Can you share some sample data and your desired result and your calculation logic? I will use my thoughts to rewrite the measure for you.😊

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.

Best Regards,

Community Support Team _ Janey

[AAMW01]

Hello, 

Thank you for the reply.

I have managed to get the totals somewhat correct however I still have the issue of the Overhead type "Tool Room" not showing up. 

Here is my measure:

budget total 2 = SUMX (
    SUMMARIZE (
        'BudgetData21-22',
        [GroupType],
       
        "a",
            CALCULATE (
                MAX ( 'BudgetData21-22'[Value] ),
                FILTER (
                    'cwDashOverHeads-KPI10',
                    'cwDashOverHeads-KPI10'[OverheadType] = MAX('BudgetData21-22'[GroupType])
                )
            )
    ),
    [a]
)

This gets 

I do not understand why it doesn't get the Tool Room value as the 2nd column row (using a similar query) gets the tool room value.

2nd column query is:

Overhead Budget Calc = CALCULATE(SUMX(FILTER('BudgetData21-22', 'BudgetData21-22'[GroupType] = MAX('cwDashOverHeads-KPI10'[OverheadType])), 'BudgetData21-22'[Value]))

However you can see it doesn't get the correct total like in the 4th column. 

Any help as to why I cannot get the tool room value in the 4th column using summarize & an inner query?

Thank you

[v-janeyg-msft]

@AAMW01 

If you can't share some sample data, I think it's hard to help you.

Best Regards,
Community Support Team _ Janey

[AAMW01]

I found that some cells were giving blank return values.

The code below fills in those values but it essentially uses the query from column 2 but again, the total doesn't match up. 

budget total 2 = SUMX (
    SUMMARIZE (
        'BudgetData21-22',
        [GroupType]
            
    ),
    IF(ISBLANK(CALCULATE (
                SUM ( 'BudgetData21-22'[Value] ),
                FILTER (
                    'cwDashOverHeads-KPI10',
                    'cwDashOverHeads-KPI10'[OverheadType] = 'BudgetData21-22'[GroupType]
                )
            )), CALCULATE(SUMX(FILTER('BudgetData21-22', 'BudgetData21-22'[GroupType] = MAX('cwDashOverHeads-KPI10'[OverheadType])), 'BudgetData21-22'[Value])),
             (CALCULATE (
                SUM ( 'BudgetData21-22'[Value] ),
                FILTER (
                    'cwDashOverHeads-KPI10',
                    'cwDashOverHeads-KPI10'[OverheadType] = 'BudgetData21-22'[GroupType]
                )
            )))
)

This gives 

If there is a way to just SUM column 2's measure I'd love that as I think its easier than column 4's measure.

Issue with column 2's measure: doesn't total correctly.
Issue with column 4's measure: has some blank cells for some reason but does total correctly.

[AAMW01]

Budget Table
GroupType	KPIType	FY	Date	Value
Domestic	OH	FY20-21	01-Nov-20	1000
business development	OH	FY20-21	01-Nov-20	200
Marketing	OH	FY20-21	01-Nov-20	2000
Quality	OH	FY20-21	01-Nov-20	2000
Financial	OH	FY20-21	01-Nov-20	11000
Administration	OH	FY20-21	01-Nov-20	30000
IT	OH	FY20-21	01-Nov-20	12000
Design	OH	FY20-21	01-Nov-20	35000
Tool Room	OH	FY20-21	01-Nov-20	1000
Sales	OH	FY20-21	01-Nov-20	12000
Production	OH	FY20-21	01-Nov-20	12000
Engineering	OH	FY20-21	01-Nov-20	-400
establishment costs	OH	FY20-21	01-Nov-20	30000

Overhead Table
AccID	GoodsValue	TransactionDate	OverheadType
3000	0.5	01/11/2020	Domestic
3000	25	02/11/2020	business development
3000	11	03/11/2020	Marketing
3000	1.04	04/11/2020	Quality
3000	0.52	05/11/2020	Financial
2500	1.99	06/11/2020	Administration
2500	2.4	07/11/2020	IT
2500	5.3	08/11/2020	Design
2500	21	09/11/2020	Tool Room
2100	15	10/11/2020	Sales
2100	19	11/11/2020	Production
2100	11.14	12/11/2020	Engineering
2100	4.7	13/11/2020	establishment costs

So the overhead table is based on transactions and the budget table is based on overhead totals for a time period.

Both tables link to a calendar table which is not an issue.

The visual uses the Overhead type as a row, fiscal year comparison as a column (which is connected to the calendar table), Budget column which uses the measure above (which doesnt use the summarize function but gives tool room's value), the variance is just a column - column function and the budget total 2 column is the measure shown above (using the summarize, but doesn't give the Tool Room value)

Calendar Table is 

Calendar Table = ADDCOLUMNS(
    CALENDAR(DATE(2003,01,01), DATE(2025,01,01)),
    "Year", YEAR([Date]),
    "Month Year Num", CONCATENATE(YEAR([Date]), FORMAT([Date],"MMM")),
    "Month Num", MONTH([Date]),
    "Month", FORMAT([Date], "MMM"),
    "Quarter Num", FORMAT([Date], "Q"),
    "Quarter", CONCATENATE("Q", FORMAT([Date], "Q")),
    "DAY", FORMAT([Date], "D"),
    "Week", FORMAT([Date], "WW")
    
    )

and FY columns are 

FiscalYearNumber = If( Month([Date]) >= 6  , Year([Date]),Year([Date]) -1 )

FiscalYearDisplay = "FY"&Right(Format([FiscalYearNumber],"0#"),2)&"-"&Right(Format([FiscalYearNumber]+1,"0#"),2)