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:   CATEGORY AMOUNT Category 1 Measure 1 Category 2 Measure 2 Category 3 Mea...
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

v-ljerr-msft's avatar
v-ljerr-msft
Microsoft 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
    Microsoft 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