Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Data transformation

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.

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

     

    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.

2 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi Anonymous ,

       

      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.