Forum Discussion

Chanleakna123's avatar
Chanleakna123
Post Prodigy
7 years ago

How to Calculate formula

Hi everyone , i'm struggling with the calculation on Power BI , i have acheived this on Excel as below with the formula i have higlight , but i need to perform this on BI , 

I have issue with Row F , G , H on Power BI 

1st Table : i failed to calculate on Row G and H 

2nd Table : i wanna visualize the data by Class , but row F , it keeps Minus the data ( =D2-E2 ) , instead of =Sum(F2:F3)

 

Yellow highlights are where i have to calculate on Power BI , can you all help me to settle this ? 

 

Tutorial Requests

2 Replies

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

    Chanleakna123 ,

     

    In power bi, there's no index number like D2, F4, so you should create an index column(suppose start from 0).

     

    Suppose you use function =SUM(F4:F7), =IFERROR((D8-E8)/E8, "") and =IFERROR(IF(F8>E8, 0, 1-F8/E8), ""), you can create three measures using DAX like pattern below:

     

    Measure1 =
CALCULATE (
    MAX ( Table[W21 Forecast Error] ),
    FILTER ( Table, Table[Index] >= 2 && Table[Index] <= 5 )
)

Measure2 =
VAR _D8 =
CALCULATE (
MAX ( Table[W21 Forecast 2019] ),
FILTER ( Table, Table[Index] = 6 )
)
VAR _E8 =
CALCULATE ( MAX ( Table[W21 2019] ), FILTER ( Table, Table[Index] = 6 ) )
RETURN
IFERROR ( ( _D8 - _E8 ) / _E8, "" )


Measure3 =
VAR _F8 =
CALCULATE (
MAX ( Table[W21 Forecast Error] ),
FILTER ( Table, Table[Index] = 6 )
)
VAR _E8 =
CALCULATE ( MAX ( Table[W21 2019] ), FILTER ( Table, Table[Index] = 6 ) )
RETURN
IFERROR ( IF ( _F8 > _E8, 0, 1 - ( _F8 / _E8 ) ), "" )

    Community Support Team _ Jimmy Tao

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

     

     

    • Chanleakna123's avatar
      Chanleakna123
      Post Prodigy
      7 years ago

      hi v-yuta-msft  , i have a lots of rows in Power BI with different Materials , 

      1st question  : is it how we use the index while i have different table to be calculated in BI 

      2nd Question : D8 or E8 , BI doesn't have , still we can perform this on BI ? i dun think we have these

      3rd question : same as Question 2 , 

       

      Can you please assist to share me BI file of your example , as what i shared the sample , please ??? 
      Appreciated your fast response. 

       

      thanks with Regards, 
      Leakna 

       

       

      Measure1 =
CALCULATE (
    MAX ( Table[W21 Forecast Error] ),
    FILTER ( Table, Table[Index] >= 2 && Table[Index] <= 5 )
)

Measure2 =
VAR _D8 =
CALCULATE (
MAX ( Table[W21 Forecast 2019] ),
FILTER ( Table, Table[Index] = 6 )
)
VAR _E8 =
CALCULATE ( MAX ( Table[W21 2019] ), FILTER ( Table, Table[Index] = 6 ) )
RETURN
IFERROR ( ( _D8 - _E8 ) / _E8, "" )


Measure3 =
VAR _F8 =
CALCULATE (
MAX ( Table[W21 Forecast Error] ),
FILTER ( Table, Table[Index] = 6 )
)
VAR _E8 =
CALCULATE ( MAX ( Table[W21 2019] ), FILTER ( Table, Table[Index] = 6 ) )
RETURN
IFERROR ( IF ( _F8 > _E8, 0, 1 - ( _F8 / _E8 ) ), "" )