Need Assistance with Referencing Dynamically Generated Columns in Power BI Report

[SinaT]

Hi,

I've just generated a report that counts orders per order status for each month. Now, I'm looking to add a new column that divides the values in column '2' by the values in the 'Total' column. The challenge here is that columns '2,' '3,' and 'Total' don't actually exist in the original source table as standalone columns. They're created in this report by manipulating the source data through Visualizations. Unfortunately, I'm struggling to find a way to reference these newly generated columns in my report.

Any help or guidance would be greatly appreciated. Thanks!

[Erokor · Accepted solution]

Sure, this can be done pretty easily (we just have to alter the filter context using CALCULATE). First I would start by creating the following Measure:

OrderID Count = 
COUNT('FlightOrder'[OrderId])

Following this I would create the following Measure:

Variance 2 to Total =
VAR _StatTwo = CALCULATE([OrderID Count], 'FlightOrder'[Status] = "2")
VAR _StatTotal = CALCULATE([OrderID Count], FILTER(ALL('FlightOrder'[Status]),NOT 'FlightOrder'[Status] IN {"0","1","4"}))
VAR _Result = DIVIDE(_StatTwo,_StatTotal)
RETURN
_Result

Then you can simply drag the Measure [Variance 2 to Total] into your visual.

The reason this works is the CALCULATE function will overwrite your Current Filter contexts from your report with the selection in the [Variance 2 to Total] measure ie. 'FlightOrder'[Status] = "2". If you get an error saying it cannot compare type String to type Text, remove the quotations around the 2 and the items inside the FILTER statement in the code above (I cannot tell what data type this is by your screenshots).

It is also worth noting, that this will not dynamically adjust for the _StatTotal piece, it will always be a constant based on the filter criteria you are showing (regardless if you adjust it). I assumed that you would want to keep your "Total" to be that of what you are showing, regardless of your filtering.

Edit: I did notice you had a filter, filtering out other status'. I've updated the measure "Variance 2 to Total" to reflect this.

[SinaT]

Thanks Erokor! it was a great help.