Dynamic custom column workaround

[aucklanduser]

Hi all,

I realise that custom columns are only generated at data refresh so can't be used with slicer selectvalues however I am at a loss as to how to incorporate the slicer selection into my model at a ROW by ROW basis. A measure for my modelling (that I have tried) will not work as it aggregates the total and applies it rather than to each line.

I am trying to do a what if scenario analysis on commission rates on invoices. Our model is trying to show that if a level of gross margin is achieved, commission is generated at the slicer value above that (eg SelectedMargin%Value - InvoiceMargin% = Commissionable%). I currently have all the calculations working as expected via measures, this however doesnt net me the result that I want as the total gets aggregated and when I show this at a total level (by branch).

Any ideas would be much appreciated! Cheers

[v-yulgu-msft]

Hi @aucklanduser,

I would suggest you firstly have a look at Measure and Row Context.

Row context and Filter context in DAX

Power BI/PowerPivot: What is Row Context?

In your scenario, to make measures to display results at a row by row basis rather than an aggregated value, you shuold add an extra field (any type is available) which is not aggregated into the visual. Please see the difference in below screenshot.

Best regards,

Yuliana Gu

[aucklanduser · Accepted solution]

Thanks for the reply Yuliana, this doesnt work unfortunately with my data as I need to run a calculation at row level then aggregate into categories.

I have found a solution by making a measure with a combintion of SUMX and ADDCOLUMNS. This runs the calculation by row level and can still be aggregated by category and run the sum correctly (rather than just running the calculation on the category totals).

[edhans]

You can use a Calculated Column as a slicer. It is one of the few times using a calculated column makes sense, assuming you cannot get the same result in the source table (via Power Query, or at the true data source).

You cannot use a measure as a slicer though.

[aucklanduser]

Hey edhans,

Sorry, not meaning use it as a slicer but have the slicer value drive the measure/calculated column. 

Below is an example of my working measures that I cant replicate as columns