cancel
Showing results 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

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

German Luis

1 ACCEPTED SOLUTION
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] )

3 REPLIES 3
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

thanks !!!

German Luis