cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Resolver II
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 -

August_Issues 1.PNG

August_Issues 2.PNG

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
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.

View solution in original post

3 REPLIES 3
Highlighted
Super User IX
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/



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Super User VII
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.






Did I answer your question? Mark my post as a solution.

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





Highlighted
Resolver II
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.

View solution in original post

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Kudoed Authors