Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |