Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I'm trying to create a calculated column in my dataset using the following formula:
Solved! Go to Solution.
hi @Anonymous
First, you should know that:
1. Calculation column/table not support dynamic changed based on filter or slicer, it is static.
2. Measure can be affected by filter/slicer, so you can use it to get dynamic summary result.
https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/
Second, You could use DIVIDE Function in your formula as below:
https://docs.microsoft.com/en-us/dax/divide-function-dax
https://docs.microsoft.com/en-us/power-bi/guidance/dax-divide-function-operator
Column=DIVIDE('column1'[Hours_Actual],'column2'[hours_budget],0)
and for measure you could use this formula
Measure=DIVIDE(SUM('column1'[Hours_Actual]),SUM('column2'[hours_budget]),0)
Regards,
Lin
Cool, Thanks v-lili6-msft. The DIVIDE Function worked a treat!!!!
I have a problem the opposite that i would like to show "NaN" in calculated column.
I have the data in excel below:
Site Code | TA Score |
B0795 | 0.90 |
STUC_1391 | n/a |
In Power BI, "n/a" turns out showing "blank" in "Data" view and considered as "0", but actually i want to show it as "NaN".
@v-lili6-msft What i can do to change it?
or any people can advise?
Many thanks!!!!
hi @Anonymous
First, you should know that:
1. Calculation column/table not support dynamic changed based on filter or slicer, it is static.
2. Measure can be affected by filter/slicer, so you can use it to get dynamic summary result.
https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/
Second, You could use DIVIDE Function in your formula as below:
https://docs.microsoft.com/en-us/dax/divide-function-dax
https://docs.microsoft.com/en-us/power-bi/guidance/dax-divide-function-operator
Column=DIVIDE('column1'[Hours_Actual],'column2'[hours_budget],0)
and for measure you could use this formula
Measure=DIVIDE(SUM('column1'[Hours_Actual]),SUM('column2'[hours_budget]),0)
Regards,
Lin
Have you looked at the IfError?
If the calculation shows an error then it will show whatever you want it to show, otherwise it will show you the result of the division.
https://docs.microsoft.com/en-us/dax/iferror-function-dax
PercentHoursRemaining = IFERROR ('column1'[Hours_Actual]) / ('column2'[hours_budget], 0)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |