Forum Discussion

Baji_Komara's avatar
Baji_Komara
Frequent Visitor
3 years ago
Solved

Inversion of progression triangles

Hi,

I used to create a month on month progression of orderd ID's ordered in specific month and i used to get everything in a triangular table as shown in the screenshot ( 1st triangle data "refer screenshot") but looking at it we don't get the clear visualization so we used to invert the triangle (2nd triangle data "refer screenshot") and all the inversion is being done in excel by shifting the values to the left.

To get inversion triangle in powerbi i tried by creating the new columns for every month by writing the dax code as shown below.

 

column "1" = IF('Inw_outw_CV_PC_TT cmplt'[fin_mon] = "2" && ISBLANK('Inw_outw_CV_PC_TT cmplt'[April] ), 'Inw_outw_CV_PC_TT cmplt'[Upto May],
IF('Inw_outw_CV_PC_TT cmplt'[fin_mon] = "3" && ISBLANK('Inw_outw_CV_PC_TT cmplt'[April] ), 'Inw_outw_CV_PC_TT cmplt'[Upto JUne],
IF('Inw_outw_CV_PC_TT cmplt'[fin_mon] = "4" && ISBLANK('Inw_outw_CV_PC_TT cmplt'[April] ), 'Inw_outw_CV_PC_TT cmplt'[upto july],
IF('Inw_outw_CV_PC_TT cmplt'[fin_mon] = "5" && ISBLANK('Inw_outw_CV_PC_TT cmplt'[April] ), 'Inw_outw_CV_PC_TT cmplt'[Upto Aug],
IF('Inw_outw_CV_PC_TT cmplt'[fin_mon] = "6" && ISBLANK('Inw_outw_CV_PC_TT cmplt'[April] ), 'Inw_outw_CV_PC_TT cmplt'[Upto Sep],
IF('Inw_outw_CV_PC_TT cmplt'[fin_mon] = "7" && ISBLANK('Inw_outw_CV_PC_TT cmplt'[April] ), 'Inw_outw_CV_PC_TT cmplt'[Upto Oct], 'Inw_outw_CV_PC_TT cmplt'[April] ))))))
 
 
Column "2" 
= IF('Inw_outw_CV_PC_TT cmplt'[fin_mon] ="2",'Inw_outw_CV_PC_TT cmplt'[Upto JUne],
IF('Inw_outw_CV_PC_TT cmplt'[fin_mon]="3" && ISBLANK('Inw_outw_CV_PC_TT cmplt'[Upto May]),'Inw_outw_CV_PC_TT cmplt'[upto july] ,
IF('Inw_outw_CV_PC_TT cmplt'[fin_mon] = "4" && ISBLANK('Inw_outw_CV_PC_TT cmplt'[Upto JUne] ), 'Inw_outw_CV_PC_TT cmplt'[Upto Aug] ,
IF('Inw_outw_CV_PC_TT cmplt'[fin_mon] = "5" && ISBLANK('Inw_outw_CV_PC_TT cmplt'[Upto JUne] ), 'Inw_outw_CV_PC_TT cmplt'[Upto Sep] ,
IF('Inw_outw_CV_PC_TT cmplt'[fin_mon] = "6" && ISBLANK('Inw_outw_CV_PC_TT cmplt'[upto july] ), 'Inw_outw_CV_PC_TT cmplt'[Upto Oct] ,'Inw_outw_CV_PC_TT cmplt'[Upto May])))))
 
and so on upto column 6 
 

i wnated to know is it possible for triangular inversion without writing dax code if it is not possible how do we do that by writing the dax code without creating the new columns or measures like 1,2,3,4,5.... .

The outcome expecting should be the same as second triangle

 

Here is the screenshot for reference

 

 

Need Help
  • v-jianboli-msft's avatar
    v-jianboli-msft
    3 years ago

    Hi Baji_Komara ,

     

    Please try:

    First create a new table and unpivot the data:

    Output:

    Then create measures like these:

    1 = 
VAR _a =
    MAX ( 'Table'[fin_mon] )
VAR _b =
    SWITCH (
        _a,
        1, "April",
        2, "Upto May",
        3, "Upto JUne",
        4, "upto july",
        5, "Upto Aug",
        6, "Apto Sep",
        7, "Upto Oct"
    )
RETURN
    CALCULATE (
        MAX ( 'Table (2)'[Value] ),
        FILTER ( 'Table (2)', [Attribute] = _b )
    )

2 = 
VAR _a =
    MAX ( 'Table'[fin_mon] ) + 1
VAR _b =
    SWITCH (
        _a,
        1, "April",
        2, "Upto May",
        3, "Upto JUne",
        4, "upto july",
        5, "Upto Aug",
        6, "Apto Sep",
        7, "Upto Oct"
    )
RETURN
    CALCULATE (
        MAX ( 'Table (2)'[Value] ),
        FILTER ( 'Table (2)', [Attribute] = _b )
    )

    Final output:

    Best Regards,

    Jianbo Li

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

  • v-jianboli-msft's avatar
    v-jianboli-msft
    3 years ago

    Hi Baji_Komara ,

     

    1. You can try to umpivot your data and add them into a matrix visual, then create a measure for them.

    2. If you want the calculayed columns in power query editor, you need to create the columns in power query editor(using M code), the columns you created using DAX won't appear in query editor.

    3. You can't unpivot the columns using DAX directly. If you want to unpivot them using DAX you may need to create a new table(using DAX). The easiest way is using unpivot functioon in power query editor.

     

    Besides, these questions are beyond the topic discussed at the beginning of this thread. If the replies in the thread are helpful to you, please consider marking the replies that are helpful to you and creating a new thread for each question, which will make the thread more relevant and allow more people to help you. And other people with similar problems will be better able to get help from the post.

    Thanks in advance!

     

    Best Regards,

    Jianbo Li

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

4 Replies

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi Baji_Komara ,

     

    1. You can try to umpivot your data and add them into a matrix visual, then create a measure for them.

    2. If you want the calculayed columns in power query editor, you need to create the columns in power query editor(using M code), the columns you created using DAX won't appear in query editor.

    3. You can't unpivot the columns using DAX directly. If you want to unpivot them using DAX you may need to create a new table(using DAX). The easiest way is using unpivot functioon in power query editor.

     

    Besides, these questions are beyond the topic discussed at the beginning of this thread. If the replies in the thread are helpful to you, please consider marking the replies that are helpful to you and creating a new thread for each question, which will make the thread more relevant and allow more people to help you. And other people with similar problems will be better able to get help from the post.

    Thanks in advance!

     

    Best Regards,

    Jianbo Li

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

    • Baji_Komara's avatar
      Baji_Komara
      Frequent Visitor
      3 years ago

      Hi v-jianboli-msft ,

      Thanks for your response. yeah i would be creating new threads from next time onwards which would help many people

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi Baji_Komara ,

     

    Please try:

    First create a new table and unpivot the data:

    Output:

    Then create measures like these:

    1 = 
VAR _a =
    MAX ( 'Table'[fin_mon] )
VAR _b =
    SWITCH (
        _a,
        1, "April",
        2, "Upto May",
        3, "Upto JUne",
        4, "upto july",
        5, "Upto Aug",
        6, "Apto Sep",
        7, "Upto Oct"
    )
RETURN
    CALCULATE (
        MAX ( 'Table (2)'[Value] ),
        FILTER ( 'Table (2)', [Attribute] = _b )
    )

2 = 
VAR _a =
    MAX ( 'Table'[fin_mon] ) + 1
VAR _b =
    SWITCH (
        _a,
        1, "April",
        2, "Upto May",
        3, "Upto JUne",
        4, "upto july",
        5, "Upto Aug",
        6, "Apto Sep",
        7, "Upto Oct"
    )
RETURN
    CALCULATE (
        MAX ( 'Table (2)'[Value] ),
        FILTER ( 'Table (2)', [Attribute] = _b )
    )

    Final output:

    Best Regards,

    Jianbo Li

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

  • Baji_Komara's avatar
    Baji_Komara
    Frequent Visitor
    3 years ago

    hi v-jianboli-msft ,

    Thanks for your response. i have three queries now

    • Is there a way i can create the inverted table without creating 7 measures
    •  i have created a calculated columns using dax code in data view but they are not appearing in query editor is there a way to get those calculated columns in query editor
    • How to unpivot the columns in data view by using dax code