How to unpivot columns using power bi dax and not power query

[binayjethwa]

Hi ,

For sample , i have the following data , Basically i have multiple columns where i need to take only selected columns and create a table out of it , I used summarize and extracted the columns I needed , But since its a table created using dax i cannot edit it in power query , so i need to use dax to unpivot columns and get my data as below. Please help 

EMP ID	Name	Country	Sales
123	Binay	India	10
123	Binay1	Malaysia	10
124	Binay3	Singapore	11
125	Binay4	Thailand	12
126	Binay5	Philliphines	13

I want to have a table like below . 

EMP ID	Name	India	Malaysia	Singapore	Thailand	Philliphines
123	Binay	10
123	Binay1		10
124	Binay3			11
125	Binay4				12
126	Binay5					13

[PaulDBrown]

You can use the matrix visual as follows:

If you need the countries sorted, you can add a sorting colum (preferably using a dimension table). Yoou can also turn off the subtotals/totals

[binayjethwa]

@PaulDBrown  I will not be able to use matrix as i need to show around 25 fields in row section and i will have scroll issue, hence i need to use table visual only . 

[Arul]

@binayjethwa ,

Try the below DAX code in calculated table, 

Unpivot Table = 
VAR _tempTable =
    ADDCOLUMNS (
        'Table (7)',
        "@India", CALCULATE ( MAX ( 'Table (7)'[Sales] ), 'Table (7)'[Country ] = "India" ),
        "@Malaysia", CALCULATE ( MAX ( 'Table (7)'[Sales] ), 'Table (7)'[Country ] = "Malaysia" ),
        "@Singapore", CALCULATE ( MAX ( 'Table (7)'[Sales] ), 'Table (7)'[Country ] = "Singapore" ),
        "@Thailand", CALCULATE ( MAX ( 'Table (7)'[Sales] ), 'Table (7)'[Country ] = "Thailand" ),
        "@Philliphines", CALCULATE ( MAX ( 'Table (7)'[Sales] ), 'Table (7)'[Country ] = "Philliphines" )
    )
VAR _selectColumns =
    SELECTCOLUMNS (
        _tempTable,
        "Emp ID", [EMP ID],
        "Name", [Name ],
        "India", [@India],
        "Malaysia", [@Malaysia],
        "Singapore", [@Singapore],
        "Thailand", [@Thailand],
        "Philliphines", [@Philliphines]
    )
RETURN
    _selectColumns

[binayjethwa]

Hi @Arul ,

I tried the same way , basically i have a large table , from which i need those columns and country wise sales . the output i receive is having multiple rows for each country , is there any way  we can add all the values for that country and show as  sum up value. 

I want result in this way if there are multipe rows for particular column , it should sum up and display per emp , one row with sales value.  Let me know if we can workaround this.

EMP ID	Name	India	Malaysia	Singapore	Thailand	Philliphines
123	Binay	10
128	Binay1		10
124	Binay3			11
125	Binay4				12
126	Binay5					13

[Anonymous · Accepted solution]

Hi @binayjethwa ,

Please try this formula of the calculated table.

Pivot Table =
VAR _tempTable =
    ADDCOLUMNS (
        'Table',
        "@India",
            CALCULATE (
                SUM ( 'Table'[Sales] ),
                FILTER (
                    'Table',
                    [EMP ID] = EARLIER ( 'Table'[EMP ID] )
                        && [Name] = EARLIER ( 'Table'[Name] )
                        && [Country] = "India"
                )
            ),
        "@Malaysia",
            CALCULATE (
                SUM ( 'Table'[Sales] ),
                FILTER (
                    'Table',
                    [EMP ID] = EARLIER ( 'Table'[EMP ID] )
                        && [Name] = EARLIER ( 'Table'[Name] )
                        && [Country] = "Malaysia"
                )
            ),
        "@Singapore",
            CALCULATE (
                SUM ( 'Table'[Sales] ),
                FILTER (
                    'Table',
                    [EMP ID] = EARLIER ( 'Table'[EMP ID] )
                        && [Name] = EARLIER ( 'Table'[Name] )
                        && [Country] = "Singapore"
                )
            ),
        "@Thailand",
            CALCULATE (
                SUM ( 'Table'[Sales] ),
                FILTER (
                    'Table',
                    [EMP ID] = EARLIER ( 'Table'[EMP ID] )
                        && [Name] = EARLIER ( 'Table'[Name] )
                        && [Country] = "Thailand"
                )
            ),
        "@Philliphines",
            CALCULATE (
                SUM ( 'Table'[Sales] ),
                FILTER (
                    'Table',
                    [EMP ID] = EARLIER ( 'Table'[EMP ID] )
                        && [Name] = EARLIER ( 'Table'[Name] )
                        && [Country] = "Philliphines"
                )
            )
    )
VAR _selectColumns =
    SELECTCOLUMNS (
        _tempTable,
        "Emp ID", [EMP ID],
        "Name", [Name],
        "India", [@India],
        "Malaysia", [@Malaysia],
        "Singapore", [@Singapore],
        "Thailand", [@Thailand],
        "Philliphines", [@Philliphines]
    )
RETURN
    DISTINCT ( _selectColumns )

As you can see, I added a few rows to your sample data to show the sum up in the output.

Before:

After:

By the way, this conversion should be pivot, and unpivot is the case of the other way around.

Pivot and Unpivot with Power BI - RADACAD

Best Regards,

Stephen Tao

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

[Anonymous]

Hi @Anonymous 

I am trying to create similar values in which i have few columns and i want to see if value in Key Figure column is equal to "Requested Sale Order" then it should only give me the values of two other coulmns called "Requested Shipped Order and "Requested Shipped Order Value" Similarly if key figure is equal to "Shipped Sales Order " then it should populate values of "Shipped Sales " and "Shipped Sales Order value " .

I try this in Matrix table but as soon as i enterd the values its showing me other columns as well ,please see below for better explanation :

My Desire Result is :

Request you to please help me if its possible:

Thanks,

Ashish