Apply calculated measure to rows in a table based on conditions of each row

[Jagdeep1993]

Hi there,

I have a table of columns, Year, Month and Quarters and i'd like to apply a calculated MEASURE to the table that SUMs a series of variables, but it will be a different combination of variables that should be summed for each row in the table, based on the criteria of that row.

In my table of Year, Month and Quarters, i have created a 'NewVisits' column, which i've created as a MEASURE - the first row in this column should sum my measures 'Measure A, Measure B, Measure C' based on the criteria that row 1 contains 'Quarter 1'. For row 2, this should sum my measures 'Measure D, Measure E, Measure F' based on the critera that this row contains  Quarter 2' - but for some reason, the entire column seems to only apply the calculation i've created for row 1.

I've used the CALCULATE function to filter to [Quarters] = 1, but this still applies the logic to the whole column.

Is there a way I can put multiple calculations into the same measure that enables me to SUM different measures based on the row criteria?

Thank you

Jag

[amitchandak]

The information you have provided is not making the problem clear to me. Can you please explain with an example. If possible please share a sample pbix file after removing sensitive information.
Thanks.

My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[Jagdeep1993]

See image below. The column 'NewVisits' is a MEASURE that sums a series of other measures.

In Row 1, where the Quarter is 1, this row needs to SUM the MEASURES 'Measure A, Measure B, Measure C'.

In Row 2, where the Quarter is 2, this row needs to SUM the MEASURES 'Measure D, Measure E, Measure F'.

At the moment, when I use the CALCULATE function, it seems to apply the results of Row 1 to all the rows, but i don't want this.

How can I get it to show the different MEASURE calculations for each row?

[amitchandak · Accepted solution]

Not tested but try

sumx(Table, IF(MAX(table[QTR])=1,measure1+measure2+measure3,IF(max(table[QTR])=2,measure4+measure5+measure6,Measure7)))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[Jagdeep1993]

@amitchandak 

See below image - in the 'NewVisits' column (which is a MEASURE), this should have a different value for each ROW in the table.

So for the first row, that has '1' within the Quarters column, this should SUM my other measures, Measure A, Measure B and Measure C.

For the second row in the table that has the '2' in the Quarters column this should SUM my measures, Measure D, Measure E and Measure F.

However, as you can see from the image, all the rows have the same value, which is the sum of Measure A, Measure B and Measure C.

How can I write a measure that calculates the right combination of variables for each row, dependent on the criteria i have in the Quarters Column?