Forum Discussion

SinaT's avatar
SinaT
New Member
2 years ago
Solved

Need Assistance with Referencing Dynamically Generated Columns in Power BI Report

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. T...
  • Erokor's avatar
    2 years ago

    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.