Forum Discussion

Chirantan's avatar
Chirantan
Frequent Visitor
8 years ago
Solved

Calculation: Row Subtotal

Hi,   I have the follwing matrix: For each date I have a Value, Inc(increase in Value from last year) and %inc(Percentage increase from last year). Both Inc and %Inc are calculated columns. ...
Need Help
Chirantan's avatar
Chirantan
Frequent Visitor
8 years ago

v-cherch-msftSorry, I don't get it. What I did was convert the calculated columns(in and %inc) into measures this did help to correct the group level summarization.I probably didnot explain the question correctly.

Maybe the following visual would make it more clear(The following is a matrix).

But as you can see in the 'TOTAL' row, inc is -48,443(instead of -48519),i.e. Value(30 June 2014) - Value(30 June 2013) and the %inc is -327.49(instead of -328.00),i.e Value(30 June 2014) - Value(30 June 2013)/ABS( Value(30 June 2014))

v-cherch-msft's avatar
v-cherch-msft
Microsoft Employee
8 years ago

Hi Chirantan

 

You may try to use IF Function to change the total of the Matrix. There're some cases for your reference as below. By the way, you may close the row subtotal from 'Format'->'Subtotal'->'Row subtotals'.

 

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

For example:

Measure =
IF (
    SUMX (
        FILTER ( ALL ( Table1 ), Table1[date] = DATE ( 2014, 6, 30 ) ),
        Table1[Value]
    )
        = SUM ( Table1[Value] ),
    1,
    0
)

Regards,

Cherie