Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Create a table with a different measure for each row

Hello,

 

I would like to create a table with a different measure for each row, like the following table:

 

CATEGORYAMOUNT
Category 1Measure 1
Category 2Measure 2
Category 3Measure 3
Category 4Measure 4
Category 5Measure 5
Category 6Measure 6



Is this possible to do? If it is possible, how could he do it?

 

Thank you very much and best regards.

Need Help
  • Salvador's avatar
    Salvador
    9 years ago

    Hi,

     

    With "If" statements? (if category = category 1; measure 1; if cat= cat 2.... etc)

  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi Anonymous,

     

    In addition, you should also be able to use SWITCH function in this scenario. The formula below is for your reference. :smileyhappy:

    AMOUNT =
IF (
    HASONEVALUE ( 'Table1'[CATEGORY] ),
    SWITCH (
        VALUES ( 'Table1'[CATEGORY] ),
        "Category 1", [measure1],
        "Category 2", [measure2],
        "Category 3", [measure3],
        "Category 4", [measure4],
        "Category 5", [measure5],
        "Category 6", [measure6],
        0
    )
)

     

    Regards

7 Replies

  • Salvador's avatar
    Salvador
    Icon for Responsive Resident rankResponsive Resident
    9 years ago

    Hi,

     

    With "If" statements? (if category = category 1; measure 1; if cat= cat 2.... etc)

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi Anonymous,

     

    In addition, you should also be able to use SWITCH function in this scenario. The formula below is for your reference. :smileyhappy:

    AMOUNT =
IF (
    HASONEVALUE ( 'Table1'[CATEGORY] ),
    SWITCH (
        VALUES ( 'Table1'[CATEGORY] ),
        "Category 1", [measure1],
        "Category 2", [measure2],
        "Category 3", [measure3],
        "Category 4", [measure4],
        "Category 5", [measure5],
        "Category 6", [measure6],
        0
    )
)

     

    Regards

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Hi v-ljerr-msft,

       

      If I understand correctly, the amount formula you are doing is creating a column, but my question is how to create the table with the Category column.

       

      Thank you and best regards.

      • v-ljerr-msft's avatar
        v-ljerr-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        Hi Anonymous,

         

        Where is the Category column from? Is it a column of one of your table in your mode?

         

        If that is the case, you can firstly use the the formula below to create a measure.

        Measure1 =
IF (
    HASONEVALUE ( 'CatagoryTable'[Catagory] ),
    SWITCH (
        VALUES ( 'CatagoryTable'[Catagory] ),
        "Category 1", [measure1],
        "Category 2", [measure2],
        "Category 3", [measure3],
        "Category 4", [measure4],
        "Category 5", [measure5],
        "Category 6", [measure6],
        0
    )
)

        Then you should be able to use the formula below to create the table with the Category column. :smileyhappy:

        Table = SUMMARIZE ( 'CatagoryTable', 'CatagoryTable'[Catagory], "Amount", [Measure1] )

         

        Regards