Need support in DAX

[Anonymous]

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

[v-xuding-msft]

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]

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

[v-xuding-msft]

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]

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

[Anonymous]

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
KALLI331834	939842	XE1
KALLI331845	365377	EN1
	331386	EN1
KALLI333963	116971	EN1
KALLI333967	91700	EN1
KALLI331163	78474	XE1
KALLI333768	53712	XE1
KALLI333491	49999	XE1
KALLI333461	33392	IFN
	33111	XE1
KALLI333966	33464	EN1
KALLI333964	31435	EN1
KALLI333815	31116	IFN
KALLI333977	37972	EN1
	10000	IFN
KALLI333496	8112	XE1
KALLI331791	6692	XE1
KALLI333736	6315	XE1
KALLI333961	5199	EN1
KALLI331747	3582	IFN
KALLI333976	3134	EN1
KALLI333114	1111	EN1
KALLI333493	-3750	AC1
	-13319	XE1
KALLI333467	-35355	AC1
KALLI331771	-31191	AC1
	-31465	XE1
KALLI119717	-88151	AC1
KALLI333741	-91887	XE1
	-135396	XE1
KALLI331953	-153874	AC1
KALLI331851	-165862	AC1
KALLI331733	-337887	AC1
	-337939	AC1

Thank you very much 

Murali