The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Solved! Go to 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
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...
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?
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
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?
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
94 | |
81 | |
63 | |
56 |
User | Count |
---|---|
247 | |
122 | |
110 | |
77 | |
72 |