Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 am...
Need Help
  • d_gosbell's avatar
    d_gosbell
    7 years ago

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

d_gosbell's avatar
d_gosbell
Super User
7 years ago

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's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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