Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Need support in DAX

Hi All,

 

I am trying to  convert Tableau calculation in PBI, but I am not sure of FIXED function in PBI. Did anyone tried similar scenario.

 

 Tableau Calculation ( Actual calculation)

IF { FIXED [Project ID] : ([senario 2 result]) } > 0 THEN "Increased"
ELSEIF { FIXED [Project ID] : ([senario 2 result]) } < 0 THEN "Decreased"
ELSEIF { FIXED [Project ID] : ([senario 2 result)]) } = 0 THEN "No Change"
ELSE NULL
END

 

 

PBI DAX ( Tableau à PowerBI DAX)

 

senario 2 Result = SUMX ( FILTER ( Table, Table[Colomn 1] <> BLANK () ), [Column 1] ) - SUM ( [Colomn 5])

 

(I am not sure below DAX is correct )

Variance = CALCULATE( [senario 2 result)],ALLEXCEPT([Project ID]))


Increased or Decreased Variance = IF([Variance]>0,"increased",IF([Variance]<0,"Decreased",IF([Variance]=0,"No change",Blank())))

 

Final output should be as below in Table green and Table red is what i am getting in PBI

Fig 2: Category B should be in decreased bucket as it is -ve value.  But unfortunately I am getting in increased bucket.

 

Thanks in Advance.

 

Murali

Need Help

5 Replies

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

    Hi Anonymous ,

    I can’t reproduce your issue. My sample data using your measures is correct, like the picture. Maybe because your data is more complexed than mine. Can you please post the sample table? Then we can help you quickly.

     

    Best Regards,

    Xue Ding

     

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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi v-xuding-msft 

      Please find the below data and the final output should be with respect to project ID's, as i will be appling filter on this output to see the details of the projectID .

       

      Below is the calculation i have used for calculating Variance.

      Variance = CALCULATE( [senario 2 result)],ALLEXCEPT([Project ID]))


      Increased or Decreased Variance = IF([Variance]>0,"increased",IF([Variance]<0,"Decreased",IF([Variance]=0,"No change",Blank())))

       

      Project ID
      senario 2 Result      		Categorisation
      KALLI331834939842XE1
      KALLI331845365377EN1
       331386EN1
      KALLI333963116971EN1
      KALLI33396791700EN1
      KALLI33116378474XE1
      KALLI33376853712XE1
      KALLI33349149999XE1
      KALLI33346133392IFN
       33111XE1
      KALLI33396633464EN1
      KALLI33396431435EN1
      KALLI33381531116IFN
      KALLI33397737972EN1
       10000IFN
      KALLI3334968112XE1
      KALLI3317916692XE1
      KALLI3337366315XE1
      KALLI3339615199EN1
      KALLI3317473582IFN
      KALLI3339763134EN1
      KALLI3331141111EN1
      KALLI333493-3750AC1
       -13319XE1
      KALLI333467-35355AC1
      KALLI331771-31191AC1
       -31465XE1
      KALLI119717-88151AC1
      KALLI333741-91887XE1
       -135396XE1
      KALLI331953-153874AC1
      KALLI331851-165862AC1
      KALLI331733-337887AC1
       -337939AC1

       

      Thank you very much 

       

      Murali

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi v-xuding-msft 

       

      I wanted to show aggregated values for increased and decreased w.r.t categories. 

      I too get values correct when i use the project ID column. when i remove Project ID column the values are misbehaving for the increased and decreased bucket

       

      Thanks

      Murali

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

    Hi Anonymous ,

    The  measure you created is based on the ID. So it is not supported that show the correct value of increased and decreased when you remove the ID.  I create a sample using the calculated column and the column of senario 2 Result to implement.

     

    1. Create a calculated column

     

    Column = IF([Variance]>0,"increased",IF([Variance]<0,"Decreased",IF([Variance]=0,"No change",Blank())))

     

    1. Create a measure to calculate the aggregated values for increased and decreased.

     

    aggregated values = CALCULATE(COUNT(Table13[Column]),ALLEXCEPT(Table13,Table13[Column]))

    this is my pbix file you can reference.

     

    https://microsoftapc-my.sharepoint.com/:u:/g/personal/v-xuding_microsoft_com/EccoTCWdOV1PptAewLmBLEcBhNkWavfiIlVxONfeYM2ywg?e=19BScN

     

    Best Regards,

    Xue Ding

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi v-xuding-msft 

       

      The issue got resolved, I did aggregation for variance. so the values were correct when i am using Project ID field. 

      it got corrected when i removed the sum for variance 

       

      Variance = CALCULATE(Table1[Column1],ALLEXCEPT(Table1,Table1[Project ID]))
       
      Thanks for your support