Custom Subtotal

[Anonymous]

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

[d_gosbell · Accepted solution]

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] )

[Anonymous]

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

[Anonymous]

Hi , i solved IT !! my problem was that i was using a column instead of a measure for the calculated value and also use HASONEVALUE

monto probabilidad = IF(HASONEVALUE(Hoja1[Oportunidades]),sumx(Hoja1,Hoja1[Monto]),SUMX(Hoja1, (Hoja1[Probabilidad]/100)*Hoja1[Monto]))

thanks !!!

German Luis