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

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

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.