Input figure in table visual, get total of values

[michael_knight]

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

[selimovd]

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 =
ADDCOLUMNS(
    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

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[michael_knight]

Thanks @selimovd ,

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

Bonus = 
VAR vBaseTable =
ADDCOLUMNS(
    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

[selimovd]

@michael_knight 

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?

[michael_knight]

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

[v-yiruan-msft · Accepted solution]

Hi @michael_knight ,

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