report builder - using dynamically created columns in expressions

[Anonymous]

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?

[Anonymous · Accepted solution]

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))

[Icey]

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

[Anonymous]

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".