Forum Discussion
report builder - using dynamically created columns in expressions
i have a report that has a column group that supplies 2 columns dynamically: cost and sales
i want to use the results here in other expressions in adjacent columns
what is the syntax to provide for (eg. cost / sales) other expressions?
it's actually not that bad. add a column, use the scoped sum function (=Sum(Fields!YearlyIncome.Value,"GroupByInitial") where "GroupByInitial" is the name of the target group) and the name of the group and add an if statement to exclude the undesired values(all months except last month) and a group filter to exclude the parameter of the other dynamically provided column "cost". in this case i only wanted to sum if the type = sales. with no "cost" data to return, only the 1 row ("sales") is dynamically provided.
=Sum(IIF(Fields!GLPeriodBal_FiscalPeriod.Value = Month(Today) - 1,Fields!Calculated_REBalance_Amt.Value , nothing),"Domestic") / Sum(IIF(Fields!GLPeriodBal_FiscalPeriod.Value = Month(Today) - 1, Fields!Calculated_REBalance_Amt.Value, nothing))
3 Replies
Hi Anonymous ,
i have a report that has a column group that supplies 2 columns dynamically: cost and sales
How do you get this? Any rules? If you could share us a sample, it would be great.
Reference: How to Get Your Question Answered Quickly - Microsoft Power BI Community
Best Regards,
Icey
there is a column in the table that is nvar but basically a bit as it displays cost or sales. the column in the report is grouped on that value so it produces 2 columns, 1 for each value
problem is i don't know how to then use those columns (which are totaling the cost or sales value for the row) in a further column to dertermine the "margin".
it's actually not that bad. add a column, use the scoped sum function (=Sum(Fields!YearlyIncome.Value,"GroupByInitial") where "GroupByInitial" is the name of the target group) and the name of the group and add an if statement to exclude the undesired values(all months except last month) and a group filter to exclude the parameter of the other dynamically provided column "cost". in this case i only wanted to sum if the type = sales. with no "cost" data to return, only the 1 row ("sales") is dynamically provided.
=Sum(IIF(Fields!GLPeriodBal_FiscalPeriod.Value = Month(Today) - 1,Fields!Calculated_REBalance_Amt.Value , nothing),"Domestic") / Sum(IIF(Fields!GLPeriodBal_FiscalPeriod.Value = Month(Today) - 1, Fields!Calculated_REBalance_Amt.Value, nothing))
