Create a calculated column in matrix visualization

[mkim]

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. 

[Fowmy · Accepted solution]

@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 🙂

YouTube  LinkedIn

[v-yuaj-msft · Accepted solution]

Hi @mkim ，

Based on your description, you can do some steps as follows.

1. Create an index column.
2. 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.

[amitchandak]

@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

[Fowmy · Accepted solution]

@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 🙂

YouTube  LinkedIn

[newpowerbiuser8]

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