Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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

1 ACCEPTED SOLUTION
d_gosbell
Super User
Super User

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

View solution in original post

3 REPLIES 3
d_gosbell
Super User
Super User

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
Not applicable

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
Not applicable

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors