Forum Discussion
Helper IGet individual values from table1 where table1 column = table 2 column then sum
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.
Community Support
If you can't share some sample data, I think it's hard to help you.
Best Regards,
Community Support Team _ Janey
Helper I| 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)