Forum Discussion

AAMW01's avatar
AAMW01
Icon for Helper I rankHelper I
5 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 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.

 

Need Help
  • AAMW01's avatar
    AAMW01
    5 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.

     

11 Replies

  • AAMW01's avatar
    AAMW01
    Icon for Helper I rankHelper I
    5 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.

     

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    5 years ago

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

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

        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