calculations (column) on top of existing DAX measures (rows)

[AH2022]

Hi ALL,

I need to produce a report, which requires measures as rows and measures on columns.

I read about calculations groups, and usually they are about changing measures (on rows) depending on different frame times (on columns).

The requirements for this report do not just change the measures on the line based on time, but also other criteria.

For example, on rows we group different types of gifts by rows: gifts from different campaigns, gifts from different events, gifts from stock and others.

In the columns, we need to “modify” the measures on rows by status of gifts:  RECEIVED (paid), EXPECTED – signed (an agreement is signed and the gift is expected), or just opportunities that do not have a contract yet (Opportunities NOT SIGNED). Here is an example below:

On this visual, the rows contain for example,  gifts from CampaignA and CampaignB

And the columns, don’t represent a dimension of just one table (for example fiscal years). It can be GIFT from CampaignA&B, Expected or GIFT FROM A&B, Received And I have the measure for the [GiftExpected] [GIftReceived] etc

I need some advice on how to produce this table. Is it possible to have measures for rows and measures for columns? I have never done this before.

Any help would be much appreciated.

Thank you

[Anonymous · Accepted solution]

Hi  @AH2022 ,

Do you want to put Measure on the matrix, but in the matrix column and row can only add calculated column, Measure can only be added to Value.

It can only be said that you can use the name of the measure to use Enter data to recreate the two tables.

Use Switch to customize the rules, when the rules are met, it is equal to the corresponding Measure.

Measure =
SWITCH(
    TRUE(),
    MAX('matrix1'[Label])="GiftCampalgn"&&MAX('martix2'[Group])="Received 22-23",[Measure1],
    MAX('matrix1'[Label])="GiftFromEvents"&&MAX('martix2'[Group])="Received 22-23",[Measure2],
    MAX('matrix1'[Label])="GiftfromStocks+Other"&&MAX('martix2'[Group])="Received 22-23",3,
    MAX('matrix1'[Label])="GiftCampalgn"&&MAX('martix2'[Group])="Expacted 22-23",11,
    MAX('matrix1'[Label])="GiftFromEvents"&&MAX('martix2'[Group])="Expacted 22-23",22,
    MAX('matrix1'[Label])="GiftfromStocks+Other"&&MAX('martix2'[Group])="Expacted 22-23",33,   
    MAX('matrix1'[Label])="GiftCampalgn"&&MAX('martix2'[Group])="Opportunities 22-23",111,
    MAX('matrix1'[Label])="GiftFromEvents"&&MAX('martix2'[Group])="Opportunities 22-23",222,
    MAX('matrix1'[Label])="GiftfromStocks+Other"&&MAX('martix2'[Group])="Opportunities 22-23",333,
    MAX('matrix1'[Label])="GiftCampalgn"&&MAX('martix2'[Group])="Total 22-23",1111,
    MAX('matrix1'[Label])="GiftFromEvents"&&MAX('martix2'[Group])="Total 22-23",2222,
    MAX('matrix1'[Label])="GiftfromStocks+Other"&&MAX('martix2'[Group])="Total 22-23",3333
    )

Best Regards,

Liu Yang

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

[AH2022]

Thank you so much for your answer @Anonymous 

I will take time to test it this week-end.

I will accept as solution after practice. Thanks again. Much appreciated.