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 

 

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

 

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

v-janeyg-msft 

 

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

  • 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

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

    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