Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Dax query - Converting row to Columns

  • 1.  Dax query - Converting row to Columns

    Posted 24 days ago
      |   view attached
    Dear All

    Need an urgent help with an analysis i was doing on mutual funds using power bi. I have given the raw input and expected output. Please note that the rows with unique dates are converted to columns. (Enclosed excel file as well for ready ref)

    Really struggling with this. Request help

    Regards
    AP

    Input table name: inputfiles

    Group Scheme Code Net Asset Value Repurchase Price Sale Price Date Cat_1 Cat_2 Cat_3
    Open 1 43.79 43.35 43.79 30-Apr-18 Open  Growth  Dividend
    Open 2 31.67 31.35 31.67 30-Apr-18 Open  Growth  Dividend
    Open 3 51.995 51.995 51.995 30-Apr-18 Open  Equity Scheme - Multi Cap Fund  Growth
    Open 4 17.019 17.019 17.019 30-Apr-18 Open  Equity Scheme - Multi Cap Fund  Dividend
    Open 5 48.142 48.142 48.142 30-Apr-18 Open  Equity Scheme - Multi Cap Fund  Growth
    Open 1 44.6658 44.217 44.6658 30-May-18 Open  Growth  Dividend
    Open 2 32.3034 31.977 32.3034 30-May-18 Open  Growth  Dividend
    Open 3 53.0349 53.0349 53.0349 30-May-18 Open  Equity Scheme - Multi Cap Fund  Growth
    Open 4 17.35938 17.35938 17.35938 30-May-18 Open  Equity Scheme - Multi Cap Fund  Dividend
    Open 5 49.10484 49.10484 49.10484 30-May-18 Open  Equity Scheme - Multi Cap Fund  Growth
    Open 1 45.11246 44.65917 45.11246 29-Jun-18 Open  Growth  Dividend
    Open 2 32.62643 32.29677 32.62643 29-Jun-18 Open  Growth  Dividend
    Open 3 53.56525 53.56525 53.56525 29-Jun-18 Open  Equity Scheme - Multi Cap Fund  Growth
    Open 4 17.53297 17.53297 17.53297 29-Jun-18 Open  Equity Scheme - Multi Cap Fund  Dividend
    Open 5 49.59589 49.59589 49.59589 29-Jun-18 Open  Equity Scheme - Multi Cap Fund  Growth

    Output:
    Scheme Code Group Cat_1 Cat_2 Cat_3 30-Apr-18 30-May-18 29-Jun-18 Deltavs30AP Deltavs30Ma
    1 Open Open  Growth  Dividend 43.79 44.6658 45.11246 3% 1%
    2 Open Open  Growth  Dividend 31.67 32.3034 32.62643 3% 1%
    3 Open Open  Equity Scheme - Multi Cap Fund  Growth 51.995 53.0349 53.56525 3% 1%
    4 Open Open  Equity Scheme - Multi Cap Fund  Dividend 17.019 17.35938 17.53297 3% 1%
    5 Open Open  Equity Scheme - Multi Cap Fund  Growth 48.142 49.10484 49.59589 3% 1%



    Regards
    AP

    ------------------------------
    Akitnava P

    ------------------------------

    Attachment(s)

    xlsx
    test.xlsx   1.24MB 1 version


  • 2.  RE: Dax query - Converting row to Columns

    Bronze Contributor
    Posted 23 days ago
    Looks to me as though the "Pivot" command in Power Query will do what you want - see https://community.powerbi.com/t5/Community-Blog/Pivot-your-Data-using-Power-Query/ba-p/224019

    ------------------------------
    Andrew Simmans
    ------------------------------



  • 3.  RE: Dax query - Converting row to Columns

    Top Contributor
    Posted 23 days ago
    Edited by Vishesh Jain 23 days ago
      |   view attached
    Hi @Akitnava P,

    Please check the file for your desired solution.​

    Just a pointer, these are all explicit calculations and Power BI is probably not the right tool for this.

    Hope this helps.

    Thank you,

    ------------------------------
    Vishesh Jain
    Owner
    VR Construction
    ------------------------------

    Attachment(s)

    pbix
    Test.pbix   44K 1 version


  • 4.  RE: Dax query - Converting row to Columns

    Posted 23 days ago
    Thank you. It works

    ------------------------------
    Akitnava P
    Partner
    9820631595
    ------------------------------



  • 5.  RE: Dax query - Converting row to Columns

    Top Contributor
    Posted 23 days ago
      |   view attached
    Hi @Akitnava P

    I suppose you have got the solution from @Vishesh Jain

    Below is another take on it. The below solution assumes the following:

    1. You may have multiple dates coming in future;
    2. You may have to dynamically select dates to compare;
    3. The maximum date you select in the slicer is the base date against which the deltas are calculated;

    The only downside to the solution is that extra space is created between the comparing periods by the additional blank columns. Since now, we cannot have conditional column headers, I am not sure whether we can do anything about it. If the additional blank space is not an issue, please see if the following helps you:

    The DAX for Delta for the base date -1 period is below:
    Delta Latest Selected Month -1 = 
    ----------------------------------------------------------------------- 
    //dates calculations
    VAR _maxslicerDate =
        CALCULATE ( MAX ( InputTable[Date] ), ALLSELECTED ( InputTable[Date] ) ) --maximum of the slicer selected date. This is the base date
    VAR _rlvntDate =
        SELECTEDVALUE ( InputTable[Date] ) --date in the current context
    VAR __selectedSlicerDates =
        CALCULATETABLE ( VALUES ( InputTable[Date] ), ALLSELECTED ( InputTable[Date] ) ) --all the dates selected in the slicer, ignoring date filters coming in from the visual itself
    ---------------
    //since we are looking back at the immediately preceding period from the base date, we select the top2 dates
    VAR __top2Dates =
        TOPN ( 2, __selectedSlicerDates, InputTable[Date], DESC ) 
    ---------------
    VAR _maxdateminus1 =
        CALCULATE ( MIN ( InputTable[Date] ), __top2Dates ) --the minimum date from the above top2 table will be the immediately preceding date
    ------------------------------------------------------------------------
    //delta calcualtion
    VAR _baseSalePrice =
        SELECTEDVALUE ( InputTable[Sale Price] ) --get the saleprice for the base date
    VAR _rlvntSalePrice =
        CALCULATE (
            MAX( InputTable[Sale Price] ),
            ALLSELECTED ( InputTable[Date] ),
            InputTable[Date] = _maxdateminus1
        ) --get the saleprice of the immediately preceding date
    VAR _deltaminus1 =
        DIVIDE ( _baseSalePrice - _rlvntSalePrice, _rlvntSalePrice ) -- calculate the delta
    -------------------------------------------------------------------------
    RETURN
        //we show the values only for the base date, other columns return blank
        IF (
            _rlvntDate = _maxslicerDate,
            _deltaminus1,
            BLANK ()
        )
    
    ​

    You can create additional deltas by changing the TOPN value accordingly as has been done for the delta –2 measure.

    PFA the pbix file for your reference.

    ​​​

    ------------------------------
    Gopa Kumar
    Limner Consulting
    ------------------------------

    Attachment(s)