Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Calculate Branch Cost

Hello   I'm trying to sum up the values for different Dimension within the the GL Entry table. As I'm new to Power BI i think i don't use the correct Filter function or there is a smoother way to...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

     

    Since I don't know what exactly the expected results you want. I list several cases.

    Sample data.

     

     

    1.Remove the concept of grouping and return the total for each row.

    Measure = CALCULATE(SUM('AX$G_L Entry (2)'[Amount]),ALLSELECTED('AX$G_L Entry (2)'))

     

    2.Summing by grouping [Global Dimension 1 Code], ignoring the context of the year.

    Measure 2 = CALCULATE(SUM('AX$G_L Entry (2)'[Amount]),FILTER(ALLSELECTED('AX$G_L Entry (2)'),[Global Dimension 1 Code]=MAX('AX$G_L Entry (2)'[Global Dimension 1 Code])))

     

     

    3.If you only want to ask for the sum of part [Global Dimension 1 Code], you can write it like this

    Measure 3 = CALCULATE(SUM('AX$G_L Entry (2)'[Amount]),FILTER(ALLSELECTED('AX$G_L Entry (2)'),[Global Dimension 1 Code]="BM"||[Global Dimension 1 Code]="CS"))

    Summing which [Global Dimension 1 Code] is "BM" or "CS".

     

    If the above cases is still not what you want, please provide some sample data and the desired result.

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

VijayP's avatar
VijayP
Icon for Community Champion rankCommunity Champion
3 years ago

Anonymous  I just forgot remove the other filters, use this code

CALCULATE(SUM('AX$G_L Entry (2)'[Amount]),
FILTER('AX$G_L Entry (2)', 
'AX$G_L Entry (2)'[Global Dimension 1 Code] = "SA")&&
 'AX$G_L Entry (2)'[Global Dimension 1 Code] = "BM")&&
'AX$G_L Entry (2)'[Global Dimension 1 Code] = "CS"))
Anonymous's avatar
Anonymous
Not applicable
3 years ago
 now I get the following error message:
Operator or expression '()' is not supported in this context.
 
Total Branch Costs =
CALCULATE(SUM('AX AG$G_L Entry (2)'[Amount]),
FILTER('AX AG$G_L Entry (2)', 'AX AG$G_L Entry (2)'[Global Dimension 1 Code] = "SA")&&
('AX AG$G_L Entry (2)', 'AX AG$G_L Entry (2)'[Global Dimension 1 Code] = "BM")&&
('AX AG$G_L Entry (2)', 'AX AG$G_L Entry (2)'[Global Dimension 1 Code] = "CS")
VijayP