Forum Discussion
Calculated columns and measures
I need to recreate this excel formula but in a POWER BI report, ((A1/A2)*A3), I already have it but the results must be variables by date range and I used calculated columns and Measures to solve this and applied a filter to the report page and I realized that the filters did not apply to calculated columns so that filter is not being applied to the results and remain static.
(In this image the values are correct because the total hours in general is 29)
(But in this image with the date modified in the filter the values of the hours are maintained because they are made based on a calculated column to which these filters are not applied)
the operation that is performed is: (CIBERIT/Total hours (i.e. the sum of those hours))*the pnderación of each category either SMB, CORP or OTHERS, and then that result I must multiply it by the total of the sum of the hours again
The problem is that in the measures you cannot set individual values such as the times of each record to do these operations, and in the calculated columns page filters and date segmenters are not applied. I need help please.
Hi Syndicate_Admin ,
It sounds like you're trying to recreate an Excel formula in a Power BI report and are having trouble with the filters not applying to calculated columns. Have you tried using DAX formulas instead of calculated columns? DAX formulas can be used in measures and can be filtered based on date ranges. Here's an example of how you can modify your formula to use DAX:
Measure = VAR TotalHours = CALCULATE ( SUM ( Table[Hours] ), ALL ( Table ) ) VAR CIBERIT = CALCULATE ( SUM ( Table[CIBERIT] ), ALL ( Table ) ) VAR SMBWeight = CALCULATE ( SUM ( Table[Weight] ), Table[Category] = "SMB" ) VAR CORPWeight = CALCULATE ( SUM ( Table[Weight] ), Table[Category] = "CORP" ) VAR OTHERSWeight = CALCULATE ( SUM ( Table[Weight] ), Table[Category] = "OTHERS" ) RETURN ( CIBERIT / TotalHours ) * ( ( SMBWeight * 0.5 ) + ( CORPWeight * 0.7 ) + ( OTHERSWeight * 0.9 ) ) * TotalHoursIn this formula, `Table` refers to the name of your data table and `Hours`, `CIBERIT`, `Weight`, and `Category` are the names of the columns in your table. You can modify the formula to fit your specific data and calculations.
How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Community Support Team _ RongtieIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
Hi Syndicate_Admin ,
It sounds like you're trying to recreate an Excel formula in a Power BI report and are having trouble with the filters not applying to calculated columns. Have you tried using DAX formulas instead of calculated columns? DAX formulas can be used in measures and can be filtered based on date ranges. Here's an example of how you can modify your formula to use DAX:
Measure = VAR TotalHours = CALCULATE ( SUM ( Table[Hours] ), ALL ( Table ) ) VAR CIBERIT = CALCULATE ( SUM ( Table[CIBERIT] ), ALL ( Table ) ) VAR SMBWeight = CALCULATE ( SUM ( Table[Weight] ), Table[Category] = "SMB" ) VAR CORPWeight = CALCULATE ( SUM ( Table[Weight] ), Table[Category] = "CORP" ) VAR OTHERSWeight = CALCULATE ( SUM ( Table[Weight] ), Table[Category] = "OTHERS" ) RETURN ( CIBERIT / TotalHours ) * ( ( SMBWeight * 0.5 ) + ( CORPWeight * 0.7 ) + ( OTHERSWeight * 0.9 ) ) * TotalHoursIn this formula, `Table` refers to the name of your data table and `Hours`, `CIBERIT`, `Weight`, and `Category` are the names of the columns in your table. You can modify the formula to fit your specific data and calculations.
How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Community Support Team _ RongtieIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.