cancel
Showing results for
Did you mean:
Highlighted
Resolver II

## Customize the 'Total' Formula for a visual

Hi community!

I'm facing an issue in Power Bi and i am in desperate need of some help. I need some way to modify the formula used to find the total.

So, im having 2 tables which are shown below -

Table2 has been created using the Distinct formula.

``DISTINTCT('Table1'[House])``

Where,

``Mathematics = AVERAGEX(FILTER('Table1', ('Table1'[House] = 'Table2'[House]), 'Table1'[Mathematics])``

Now i need to display the Total in such a way that Total of Mathematics column should be = Sum of Marks / Total number of students

ex. Total for Mathematics = (20 + 10 + 30 + 20)/ 4

Is there any way to do this? Or can i use some sort of custom visual?

Thanks for any help!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Resolver II

## Re: Customize the 'Total' Formula for a visual

It did indeed turn out that i was over complicating things.

By skipping the creation of Table 2 completely and by using the same Dataset of Table1 to make the 2nd visual, i am able to get this result.

I gave the 'House' as the Matric Row and the Subjects as the Matrix Values and selected the average value.

Thanks all for the help! Your techniques will surely be useful to me in the future.

3 REPLIES 3
Highlighted
Super User IX

## Re: Customize the 'Total' Formula for a visual

@DevadathanK , you can try isfiltered or isinscope

if(isfiltered(Table[House]),sum('Table1'[Mathematics]),[Mathematics])

https://powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

Proud to be a Super User!

Highlighted
Super User VII

## Re: Customize the 'Total' Formula for a visual

@DevadathanK seems like you are overcomplicating your calculation, anyhow, you can use HASONEFILTER function to check if you are on total line then create a different calculation

``````Measure =
IF ( HASONEFILTER( Table[Room] ), <<your average calculation>>,
DIVIDE( SUM ( Table[MathCol] ), COUNTROWS( VALUES ( Table[Room] ) ) )
)``````

tweak the above measure as per your need.

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Highlighted
Resolver II

## Re: Customize the 'Total' Formula for a visual

It did indeed turn out that i was over complicating things.

By skipping the creation of Table 2 completely and by using the same Dataset of Table1 to make the 2nd visual, i am able to get this result.

I gave the 'House' as the Matric Row and the Subjects as the Matrix Values and selected the average value.

Thanks all for the help! Your techniques will surely be useful to me in the future.

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021