Forum Discussion

Emmy66's avatar
Emmy66
Icon for Helper V rankHelper V
3 years ago

Subtracting rows in a matrix

Hi,
Could someone please assist on this. I’m struggling to get this to work. In the screenshot below, is a matrix that I created, I want to apply this formula on NRCOS Actual = NRCOS-(NRFI-NROB-NRPDI). I created 3 measures as shown in bracket above using the dax below

 

NRINS = CALCULATE(SUM(NominalTransactions[Posting Value]),

        FILTER(NominalTransactions,NominalTransactions[Accounting Period] IN vDates && NominalTransactions[Mapping Code] IN {"NRINS",”NRFI”,”NROB”,”NRPDI”} && NominalTransactions[TransactionType]="Actual"))

 

Expected output (-6,741,364), I used the below DAX to add it to the MTD_Actual

MTD_Actual =  SUMX( VALUES(NominalTransactions[Mapping Code]), IF(NominalTransactions[Mapping Code]="NRCOS",[ NRCOS Actual],0))) but it still returned (-6,837,900)

Any help on this will be appreciated.

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Emmy66 ,

    Please update the formula of measure [MTD_Actual] as below and check if it can return the correct result...

    MTD_Actual =
SUMX (
    GROUPBY (
        NominalTransactions,
        NominalTransactions[GroupOrder],
        NominalTransactions[Mapping Code]
    ),
    [NRCOS ctual]
)

    If the above one can't help you get the desired result, please provide some sample data in your table 'NominalTransactions' (exclude sensitive data) with Text format  and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. By the way, is the [NRCOS Actual] a calculated column or measure? Thank you.

    How to upload PBI in Community

    Best Regards

    • Emmy66's avatar
      Emmy66
      Icon for Helper V rankHelper V
      3 years ago

      Anonymous thanks for your prompt response. I updated my query as you posted but it didn't fix the problem as it still returned same value of NRCOS (-400) as shown below. Please see pbix file attached 

      NRCOS Actual is a measure. I've included all in the pbix file. I also want to retain some values and others zeroed out as shown below. The value highlighted in green is the expected output for NRCOS as shown below


      thanks again for willing to assist. Much appreciated.