Forum Discussion

Vinothsusai's avatar
Vinothsusai
Icon for Helper III rankHelper III
7 years ago
Solved

Calculate formula column based on Id referred column(Non Sequence ID number) - power bi desktop

Hi, I have Formula column that each value is referred to the Id column. I need to caculate total amount based on the formula column. For ex: ReportLayout.Formula(D215) = Line ID(215)   The...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    hi, Vinothsusai 

    Just adjust the formula as below:

    Measure 4 = 
VAR maxf =
    MAX ( HANGeneralJournals_Remodify[Column 3])

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

    Result:

    For example, in your sample pbix file, the formula is "D231+D276+D284+D281+D282+D283", So I use a custom column 3 in the example:

     

    Best Regards,

    Lin

v-lili6-msft's avatar
v-lili6-msft
Icon for Community Support rankCommunity Support
7 years ago

hi, Vinothsusai 

Just adjust the formula as below:

Measure 4 = 
VAR maxf =
    MAX ( HANGeneralJournals_Remodify[Column 3])

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

Result:

For example, in your sample pbix file, the formula is "D231+D276+D284+D281+D282+D283", So I use a custom column 3 in the example:

 

Best Regards,

Lin

Vinothsusai's avatar
Vinothsusai
Icon for Helper III rankHelper III
7 years ago

Hi,

Thank you very much for your answer. It works fine. 

 

 

Thanks

Vinoth S