Forum Discussion

Vinothsusai's avatar
Vinothsusai
Helper III
7 years ago
Solved

Calculate total value based on the formula with the same aggreagated total column

Hi,

I am using a measure to find the total amount based on the formula in the table below.Here I have a doubt how can i calculate formula with the same aggreagated column amount. Could you please advise.

Formula:

Measure Formula = VAR maxf =
MAX ( HANGeneralJournals_Remodify[ReportLayout.Formula])

RETURN
IF ( maxf = BLANK () ,SUM(HANGeneralJournals_Remodify[Amount]), CALCULATE(SUM(HANGeneralJournals_Remodify[Amount]),FILTER(ALL(HANGeneralJournals_Remodify),ISBLANK([ReportLayout.Line ID])=FALSE()&&CONTAINSSTRINGEXACT(maxf,"D"&([ReportLayout.Line ID])&"|"))))
 

For example.

i take 25th line item. The formula is  D9|+D12|+D15|+D18|+D21|+D24|

For now its calculating Amount column values by using above measure= 0 + 0 + 0 + 0 + 0 + 0 = 0

But i have to take Total Amount Measure value  ie -69944.87 + 0  -13086.25 -95366.48 + 27602.27-10226.1 = 295150.290000

 

Please see the table below

Line IDGroup AccountGroup Account DescriptionMainAccountReportLayout.FormulaAmountTotal Amount
1R701000Gross sales70100000 148165942.6148165942.6
2R70100TGross sales D1|0148165942.6
3R708001Commissions to retail partner65100000 00
4R708002Regul commission to retail partner  00
5R70800TSales allowances D3|+D4|00
6R_NSNet sales D4|+D5|00
7R601001Fish purchase  00
8R603001Change in fish inventory60310001 -69944.87-69944.87
9R601001TFish & and other sea products D7|+D8|0-69944.87
10R601002Meat purchase  00
11R603002Change in meat inventory  00
12R601002TMeat D10|+D11|00
13R601003Fruits & vegetables purchase  00
14R603003Change in fruits & vegetables inventory60310003 -13086.25-13086.25
15R601003TFruits & vegetables D13|+D14|0-13086.25
16R601004Grocery purchase  00
17R603004Change in grocery products inventory60310004 -95366.48-95366.48
18R601004TGrocery D16|+D17|0-95366.48
19R601005Finished goods purchase60700001 456171.72456171.72
20R603005Change in finished goods inventory60370000 27602.2727602.27
21R601005TFinished goods D19|+D20|0483773.99
22R601006Other products purchase  00
23R603006Change in other product inventory60310005 -10226.1-10226.1
24R601006TOther products D22|+D23|0-10226.1
25R_FOODFood D9|+D12|+D15|+D18|+D21|+D24|00
26R602001Packaging purchases60261000 2204347.662204347.66

 

Thanks

Vinoht S

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi Vinothsusai 

    you could apply it in specific rows.

    Measure 2 =
VAR maxf =
    MAX ( Table1[ReportLayout.Formula] )
RETURN
    IF (
        maxf = BLANK (),
        SUM ( Table1[Amount] ),
        CALCULATE (
            SUM ( Table1[Amount] ),
            FILTER ( ALL ( Table1 ), CONTAINSSTRING ( maxf, "D" & [Line ID] & "|" ) )
        )
    )

Measure 3 =
VAR maxf =
    MAX ( Table1[ReportLayout.Formula] )
RETURN
    IF (
        MAX ( Table1[Line ID] ) <> 25,
        [Measure 2],
        SUMX (
            FILTER ( ALL ( Table1 ), CONTAINSSTRING ( maxf, "D" & [Line ID] & "|" ) ),
            [Measure 2]
        )
    )

     

     

     

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi Vinothsusai 

    you could apply it in specific rows.

    Measure 2 =
VAR maxf =
    MAX ( Table1[ReportLayout.Formula] )
RETURN
    IF (
        maxf = BLANK (),
        SUM ( Table1[Amount] ),
        CALCULATE (
            SUM ( Table1[Amount] ),
            FILTER ( ALL ( Table1 ), CONTAINSSTRING ( maxf, "D" & [Line ID] & "|" ) )
        )
    )

Measure 3 =
VAR maxf =
    MAX ( Table1[ReportLayout.Formula] )
RETURN
    IF (
        MAX ( Table1[Line ID] ) <> 25,
        [Measure 2],
        SUMX (
            FILTER ( ALL ( Table1 ), CONTAINSSTRING ( maxf, "D" & [Line ID] & "|" ) ),
            [Measure 2]
        )
    )

     

     

     

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.