Display dynamic units (in K, in M) in Power BI

Step 1: Load data to Power BI Desktop

Open Power BI desktop

 Click on use sample data to load sample data to Power BI desktop

Click on load sample data to load data to model.

Sample data looks like below

Step 2 : Create measure table

Home à click on enter data under data section

Create table as Measure

Click on load

Have created below 4 measures

Profit = SUM(financials[Profit])

Sales = SUM(financials[Sales])

Units Sold = SUM(financials[Units Sold])

Gross Sales = SUM(financials[Gross Sales])

Step 3: Create new table for display format

Go to modeling tab

Click on new table under calculations section and provide below query to new table

DisplayFormat =

DATATABLE(

    "FormatOptions",STRING,

    "Sort",INTEGER,

    {

        {"Millions",1},

        {"Thousands",2},

        {"Original Values",3}

    }

)

Click on save

Step 4: Create dynamic measures

Click on three dots (…) in measure table and create new dynamic measures

Dynamic Sales:

Dynamic Sales =

VAR FormatOption =

    SELECTEDVALUE('DisplayFormat'[FormatOptions])

VAR BaseValue = [Sales]

VAR Multiplier = SWITCH(FormatOption,

    "Millions",1000000,

    "Thousands",1000,

    "Original Values",1)

VAR AdjustedValue = BaseValue / Multiplier

RETURN AdjustedValue

Dynamic Profit:

Dynamic Profit =

VAR FormatOption =

    SELECTEDVALUE('DisplayFormat'[FormatOptions])

VAR BaseValue = [Profit]

VAR Multiplier = SWITCH(FormatOption,

    "Millions",1000000,

    "Thousands",1000,

    "Original Values",1)

VAR AdjustedValue = BaseValue / Multiplier

RETURN AdjustedValue

Dynamic GrossSales:

Dynamic GrossSales =

VAR FormatOption =

    SELECTEDVALUE('DisplayFormat'[FormatOptions])

VAR BaseValue = [Gross Sales]

VAR Multiplier = SWITCH(FormatOption,

    "Millions",1000000,

    "Thousands",1000,

    "Original Values",1)

VAR AdjustedValue = BaseValue / Multiplier

RETURN AdjustedValue

Dynamic UnitsSold:

Dynamic UnitsSold =

VAR FormatOption =

    SELECTEDVALUE('DisplayFormat'[FormatOptions])

VAR BaseValue = [Units Sold]

VAR Multiplier = SWITCH(FormatOption,

    "Millions",1000000,

    "Thousands",1000,

    "Original Values",1)

VAR AdjustedValue = BaseValue / Multiplier

RETURN AdjustedValue

 

Step 5: Bring slicer to canvas page

Drag DisplayFormat column to slice

Step 6: Create visualization

Drag and drop table to canvas page

Select required columns to table visual

Default it will show Infinity for measure values

Select values in Millions in slicer pane