Forum Discussion
Custom Subtotal
Hi All !!
Im a newbie in PowerBI and still learning so thanks for any advice you could give. My problem is simple i think i Have the following matrix and what i need is that subtotal sums the amount (column value) multiplied by the probability instead of real value , but still showing the real value in the matrix
Oportunity Probability% 201901 201902 201903
Oportunity1 20 1500 1500 1500
Oportunity2 30 5000 5000 5000
Oportunity3 50 1000 1000 1000
TOTAL 2300 2300 2300
So in regular TOTAL should be 7500 , i need to show 2300
thanks in advace
German Luis
In the following expression I'm taking a wild guess as to what some of your table and column names might be. But the general idea is that we can use IF( HASONEVALUE(...) to check if there is a single Opportunity in the current filter context. This will return true for each of the rows that has a single opportunity, but false for the grand total (where multiple opportunities are in scope)
eg.
IF ( HASONEVALUE( Opportunity[Opportunity] )
, [Amount]
, SUMX( 'Opportunity', 'Opportunity'[Probability%] * [Amount] )
3 Replies
In the following expression I'm taking a wild guess as to what some of your table and column names might be. But the general idea is that we can use IF( HASONEVALUE(...) to check if there is a single Opportunity in the current filter context. This will return true for each of the rows that has a single opportunity, but false for the grand total (where multiple opportunities are in scope)
eg.
IF ( HASONEVALUE( Opportunity[Opportunity] )
, [Amount]
, SUMX( 'Opportunity', 'Opportunity'[Probability%] * [Amount] )Hi , thanks for your answer but it still not luck so i would like to clarify the data maybe can be usefull
My data table is like this
Oportunity Probabilty Month Amount
Oportunity1 20 201901 1500
Oportunity1 20 201902 1500
Oportunity1 20 201903 1500
Oportunity2 30 201901 5000
Oportunity2 30 201902 5000
Oportunity2 30 201903 5000
Oportunity3 50 201901 1000
Oportunity3 50 201902 1000
Oportunity3 50 201903 1000
On the matrix i whant this
Oportunity Probability% 201901 201902 201903
Oportunity1 20 1500 1500 1500
Oportunity2 30 5000 5000 5000
Oportunity3 50 1000 1000 1000
TOTAL 2300 2300 2300
Totals should sum the amount * probability but in matrix shows the regular amount at row level. I tried the solution but no results yer :(
thanks for any ideas !!!
German Luis