Forum Discussion

AAMW01's avatar
AAMW01
Icon for Helper I rankHelper I
4 years ago
Solved

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

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 databa...
Need Help
  • AAMW01's avatar
    AAMW01
    4 years ago

    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.

     

v-janeyg-msft's avatar
v-janeyg-msft
Icon for Community Support rankCommunity Support
4 years ago

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's avatar
AAMW01
Icon for Helper I rankHelper I
4 years ago

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's avatar
    v-janeyg-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    AAMW01 

     

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

     

    Best Regards,
    Community Support Team _ Janey

    • AAMW01's avatar
      AAMW01
      Icon for Helper I rankHelper I
      4 years ago
      Budget Table   
      GroupTypeKPITypeFYDateValue
      DomesticOHFY20-2101-Nov-201000
      business developmentOHFY20-2101-Nov-20200
      MarketingOHFY20-2101-Nov-202000
      QualityOHFY20-2101-Nov-202000
      FinancialOHFY20-2101-Nov-2011000
      AdministrationOHFY20-2101-Nov-2030000
      ITOHFY20-2101-Nov-2012000
      DesignOHFY20-2101-Nov-2035000
      Tool RoomOHFY20-2101-Nov-201000
      SalesOHFY20-2101-Nov-2012000
      ProductionOHFY20-2101-Nov-2012000
      EngineeringOHFY20-2101-Nov-20-400
      establishment costsOHFY20-2101-Nov-2030000

       

      Overhead Table 
      AccIDGoodsValueTransactionDateOverheadType
      30000.501/11/2020Domestic
      30002502/11/2020business development
      30001103/11/2020Marketing
      30001.0404/11/2020Quality
      30000.5205/11/2020Financial
      25001.9906/11/2020Administration
      25002.407/11/2020IT
      25005.308/11/2020Design
      25002109/11/2020Tool Room
      21001510/11/2020Sales
      21001911/11/2020Production
      210011.1412/11/2020Engineering
      21004.713/11/2020establishment 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)



       

    • AAMW01's avatar
      AAMW01
      Icon for Helper I rankHelper I
      4 years ago

      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.