Forum Discussion
Create a calculated column in matrix visualization
I've been reading other forums but still am not able to figure it out. I'm looking to create a new measure subtracting the first column (2011-12) from the last column (2015-16). How do I calculate the total difference between the 2 years? When I try to create the measure, I can select that field but can't filter down to a specific year.
mkim
Adding a Measure to a Matrix will always show calculation against each Row and Column fields, to hide the values, you can create the following measure, adjust your measure accordingly:Diff = IF( ISFILTERED( Table3[YEAR] ) , "" , CALCULATE( SUM(Table3[VALUE]), Table3[YEAR] = 2016 ) - CALCULATE( SUM(Table3[VALUE]), Table3[YEAR] = 2014 ) )________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
Hi mkim ，
Based on your description, you can do some steps as follows.
- Create an index column.
- Create two date tables.
Current date = SUMMARIZE('Case3',Case3[index],Case3[DATE])
Prior date = SUMMARIZE('Case3',Case3[index],Case3[DATE])
3. Create a measure.
diff =
var cu1=MIN('Current date'[index])
var cu2=MAX('Current date'[index])
var pr1=MIN('Prior date'[index])
var pr2=MAX('Prior date'[index])
return
CALCULATE(SUM(Case3[VALUE]),'Case3'[index]>=cu1&&'Case3'[index]<=cu2)-CALCULATE(SUM(Case3[VALUE]),'Case3'[index]>=pr1&&'Case3'[index]<=pr2)
4.Create two clicers.
Result:
Hope that's what you were looking for.
Best Regards,
Yuna
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
mkim
Adding a Measure to a Matrix will always show calculation against each Row and Column fields, to hide the values, you can create the following measure, adjust your measure accordingly:Diff = IF( ISFILTERED( Table3[YEAR] ) , "" , CALCULATE( SUM(Table3[VALUE]), Table3[YEAR] = 2016 ) - CALCULATE( SUM(Table3[VALUE]), Table3[YEAR] = 2014 ) )________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
Hi amitchandak Fowmy
I am confused. I have excel table which when converted to powerbi mtarix is showing unique values for each ctagory, which is correct, the the total is showing is incorrect. It is only counting unique values however if you see, Jeans order apperas twice so it should calculate total based on unique category. I know power BI doesnt let that happen automcatlly, can you please tell a work around?
Thanks a lot
mkim , If you need such a difference dynamically, Then you need two date /year table. Refer my blog on that. It if for date range , but you can use year slicer from date table in place of Date.
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
Hi mkim ，
Based on your description, you can do some steps as follows.
- Create an index column.
- Create two date tables.
Current date = SUMMARIZE('Case3',Case3[index],Case3[DATE])
Prior date = SUMMARIZE('Case3',Case3[index],Case3[DATE])
3. Create a measure.
diff =
var cu1=MIN('Current date'[index])
var cu2=MAX('Current date'[index])
var pr1=MIN('Prior date'[index])
var pr2=MAX('Prior date'[index])
return
CALCULATE(SUM(Case3[VALUE]),'Case3'[index]>=cu1&&'Case3'[index]<=cu2)-CALCULATE(SUM(Case3[VALUE]),'Case3'[index]>=pr1&&'Case3'[index]<=pr2)
4.Create two clicers.
Result:
Hope that's what you were looking for.
Best Regards,
Yuna
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
