Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
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.
Solved! Go to 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.
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.
@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.
@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
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
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
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
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
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
If you can't share some sample data, I think it's hard to help you.
Best Regards,
Community Support Team _ Janey
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.
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)