Create a table with a different measure for each row

[Anonymous]

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	Measure 3
Category 4	Measure 4
Category 5	Measure 5
Category 6	Measure 6

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

Thank you very much and best regards.

[Salvador · Accepted solution]

Hi,

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

[v-ljerr-msft · Accepted solution]

Hi @Anonymous,

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

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]

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]

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. 

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

Regards

[Red217]

Thank you, 

you REALLY helped me

[pbiforum123]

When i am trying to create the table as you have mentioned I am getting below error. Please help...

"The function SUM cannot work with values of type String."

[pbiforum123]

Can you please attach the sample PBI which I can refer this?

I am trying to create the table as in below post. Is that possible with the above code? Please guide me..

https://community.powerbi.com/t5/Desktop/How-to-create-several-independent-calculations-over-differe...

[Salvador · Accepted solution]

Hi,

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