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

Post Prodigy

## Input figure in table visual, get total of values

Hi,

Small one but really not sure how to solve this.

I'm trying to input a value into the table visual, but the total is coming out as an average but I need it to be a sum of all of the rows

``Bonus = 600 ``

That's how I'm currently inputting the figure in

Does anyone have any ideas on how to fix this?

https://www.dropbox.com/s/4pbc79tpo9yg59a/help.pbix?dl=0

Thanks,

Mike

1 ACCEPTED SOLUTION
Community Support

You can update the formula of measure Bonus as below, please find the details in the attachment.

``````Bonus =
VAR _maxdate =
CALCULATE ( MAX ( 'Sheet1'[Date] ), ALL ( 'Sheet1'[Date] ) )
VAR _fulldate =
CALCULATE (
MIN ( 'Date'[Full Date] ),
FILTER ( 'Date', 'Date'[Month/Year] = SELECTEDVALUE ( 'Date'[Month/Year] ) )
)
RETURN
IF ( _fulldate > _maxdate, 600 ) ​``````

And you can create another new measure as below to put it on the table visual in order to make sure the total value is correct.

``Measure = SUMX(VALUES('Date'[Month/Year]),[Bonus])​``

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 REPLIES 5
Super User

Hey @michael_knight ,

you have to create an internal table that you can summarize for the total (vBaseTable in my example).

Then you return 600 if there is a filter and if it's the total you return a SUMX of the base table. Try the following measure:

``````Bonus =
VAR vBaseTable =
VALUES('Date'[Month/Year]),
"@Bonus", 600
)
RETURN
IF(
ISFILTERED('Date'[Month/Year]),
600,
SUMX(vBaseTable, [@Bonus])
)``````

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Post Prodigy

Thanks @selimovd ,

On the right track. I'm trying to add a IF statement to the bottom of this

``````Bonus =
VAR vBaseTable =
VALUES('Date'[Month/Year]),
"@Bonus", 600
)
var Filt =
IF(
ISFILTERED('Date'[Month/Year]),
600,
SUMX(vBaseTable, [@Bonus])
)

return
IF([Salary Measure] == BLANK(), Filt, BLANK()) ``````

But then it still doesn't show the total bonus again. Do you know how to combat this?

Thanks,

Mike

Super User

Why do you add this IF statement at the end? I don't really understand what it should do.

Does it work with the solution I provided?

Post Prodigy

In the main report, this bonus will be used a forecast, so will only be used for future dates. I do this by saying if Salary is blank then use the bonus measure you created

Community Support

You can update the formula of measure Bonus as below, please find the details in the attachment.

``````Bonus =
VAR _maxdate =
CALCULATE ( MAX ( 'Sheet1'[Date] ), ALL ( 'Sheet1'[Date] ) )
VAR _fulldate =
CALCULATE (
MIN ( 'Date'[Full Date] ),
FILTER ( 'Date', 'Date'[Month/Year] = SELECTEDVALUE ( 'Date'[Month/Year] ) )
)
RETURN
IF ( _fulldate > _maxdate, 600 ) ​``````

And you can create another new measure as below to put it on the table visual in order to make sure the total value is correct.

``Measure = SUMX(VALUES('Date'[Month/Year]),[Bonus])​``

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors