Forum Discussion
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.
When I switch on the Row Subtotals, I am getting the correct numbers for Value, but the inc and %inc is wrong. PowerBi is correctly doing what it is being asked to(add all values of the rows) but I need overall change/variance in the Total Row. That is inc inc = Total(Value in 30 June 2014) - Total(Value in 30 June 2014)
%inc = (Total(Value in 30 June 2014) - Total(Value in 30 June 2014))/ABS(Total(Value in 30 June 2014))
How should I approach this? Any help would be welcome.
Thanks!
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
4 Replies
Hi Chirantan
You may try to use HASONEVALUE Function (DAX) as below picture to change the value in the Total Row. Here is the reference for you.
https://community.powerbi.com/t5/Desktop/Summarizing-data/td-p/107493
Regards,
Cherie
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))
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
Hi Chirantan,
I am new to Power BI and I have a similar request like yours.I have a row called 'Segments' and three calculated columns grouped by three different plants.
Now, I want to have a calculated row 'Custom Segment'(adding two of the segments )which will then show the corresponding values ( All three calculated columns) plant wise. Below is a screenshot of my matrix. Could you please assist me with this?
Regards,
Gunjan
