Data transformation

[Satya_04]

Hi, 

I would need help on the following:

1. Convert nos. into billions. Some of them are in millions and some in billions. I would need to convert only millions to billions.

2. Calculate percentage of total for serial title. Records here are tagged to Assets and Liabilities. I would need to find serial title of an asset as a percentage of total assets. Let us say bank credit as % of total assets.

[Satya_04]

@amitchandak Please help

[v-rzhou-msft · Accepted solution]

Hi @Satya_04 ,

My Sample:

Calculated column:

Convert to Billions = 
IF('Table'[units] = "Billions of U.S. Dollars",'Table'[value],'Table'[value]/1000)

Measure:

Percentage Grouped by banks for Asset = 
VAR _Total =
    CALCULATE (
        SUM ( 'Table'[Convert to Billions] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[banks] ),
            'Table'[series_title] = "Total Assets"
        )
    )
VAR _Value =
    CALCULATE (
        SUM ( 'Table'[Convert to Billions] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[series_title], 'Table'[banks] ),
            'Table'[A/L] = "Asset"
        )
    )
RETURN
    DIVIDE ( _Value, _Total )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.