What If Parameter -- Apply to total by category, not each row for that category.

[mklangley]

Hello,

I am running into a problem applying a What If Parameter value how I need to, and am wondering if anyone can help.

I have a What If Parameter (its value, of course, can be chosen/changed by the user), and would like to apply its value to the total amount for Category = A.

Below is my data. There are two names: John and Steve. Notice that for John, there is one row for Category = A; for Steve, there are two rows for Category = A.

ID	Category	Name	Amount
1	A	John	30
2	B	John	40
3	C	John	50
4	D	John	60
5	A	Steve	70
6	A	Steve	80
7	B	Steve	90
8	C	Steve	100
9	D	Steve	110

This is my parameter: 

Parameter = GENERATESERIES(0, 10, 1)

This is Amount Plus Parameter measure:

Amount Plus Parameter = sumx('Table'

                                                    ,if('Table'[Category] = "A"

                                                       ,'Table'[Amount] + Parameter[Parameter Value]

                                                       ,'Table'[Amount]

                                                       )

                                                    )

The problem: The parameter value is being applied at the row level (since I am using SUMX); but that means that when there are multiple rows for Category = A, the Parameter value is being applied multiple times. 

For example, for John, this works--it adds the parameter value of 3 (since there is only one row for John where Category = A):

But for Steve, it is adding the parameter value twice (since there are two rows for Category = A). I would like the total to be 153, not 156.

And for All, it is applying the parameter value three times (since there are three rows for Category = A). I would like to see 183, not 189.

So in short, I would like to apply the Parameter value to the total for Category = A, not row-by-row, while still allowing the flexibility to filter by name. Is that possible?

[Anonymous · Accepted solution]

Hi @mklangley ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create a measure. 

Amount Plus Parameter = IF(MAX('Table'[Category])="A",SUM('Table'[Amount])+Parameter[Parameter Value],SUM('Table'[Amount]))

(3) Then the result is as follows.

Best Regards,

Neeko Tang

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