Dynamically Changing Measures from Amount to Quantity

RalphDodson,

Use a disconnected table that contains the measure names you want to make available to the user.  This table will be what you use for the slicer.  Next, create the measures that reference the Disconnected tables and return the users selection.  This is outlined on a couple posts on my blog, bipatterns.com.

Two posts I'd recommend checking out: Dynamic PowerBI Reports Via DAX and Compare two Stores Based on User Selected Measure.  

So if my model looks like this:

First Step:

Create the base Measures and KPI’s needed. In this example, the measures consisted of TNS (Total Net Sales), Returns, Retail Sales, Apparel Sales %, Decor Sales %, Memorabilia Sales %, etc. These are all relatively basic DAX Measures, using nothing more than Calculate, Sum, and Divide.

Step Two:

Create the disconnected tables, highlighted in red above.  These tables will consist of two columns, Position and Variable (Column or Line Variable for example).  The position column serves as a row index, while the Variable column will contain the names of the measures you want to use dynamically.

Step Three:

Create the measures that reference the Disconnected tables.

Line Measures:

Selected Line Measure Position

SelectedLinePosition =
MIN ( Line[Position] )

 

SelectedLineMeasure =
SWITCH (
    [SelectedLinePosition],
    1, [Apparel Sales %],
    2, [Décor Sales %],
    3, [Memorabilia Sales %],
    4, [Tailgate Sales %],
    5, [Online Sales %],
    6, [Retail Sales %],
    BLANK (), [Apparel Sales %] )

 

Line Measure Name =
CONCATENATE (
    “Line Measure: “,
    LOOKUPVALUE (
        Line[Line Measure],
        Line[Position], CALCULATE ( MIN ( Line[Position] ) )
    )

 

   )

 

Let me know if you have questions on anything, happy to help.

 

Thanks,

 

Ryan Durkin