CODEWITHSUNDEEP
powerbidax
Morallis
Morallis

Reputation: 151

Power BI: Multiply Grouping by Related Parameter

I'm fairly new to Power BI, so may be overlooking something basic here.

I have a Column which contains a banding number (1 to 3), depending on a customer's total spend.

Band 1: 0 - 500
Band 2: 501 - 1000
Band 3: 1001+

I have created 3 Parameters on my report (Parameter 1, 2 and 3).
Each parameter allows for a decimal range between 5 and 35 with increments of 2.5
This parameter will represent the percentage of the income that is earned by a consultant.

I'm trying to create a measure that multiplies the Income by the respective Parameter. Allowing users to adjust the percentage earning dynamically when viewing the report.

Below is an example of the table.
Table Name: Receipts

Policy Number Client Name Band Income
SA1 Ray Mann 3 800
SA2 Ray Mann 3 900
SA3 Mary Yu 2 600
SA4 Sam Fry 1 20
SA5 Sam Fry 1 50

I'm a little lost on this one.
As a calculated columns I would do a simple SWITCH statement, however as it's not a measure it will not adjust dynamically as the parameter slider is changed.

Your help is greatly appreciated.

Kind regards,
Morallis

Upvotes: 0

Views: 521

Answers (1)

Andrey Nikolov
Andrey Nikolov

Reputation: 13460

Let's say there are three what-if parameters, named Parameter1, Parameter2 and Parameter3, and your table is named Table. We can create a measure, which first will use SUMMARIZE to summarize the data calculating the total income per band:

SUMMARIZE('Table', 'Table'[Band], "Income", SUM('Table'[Income]))

Then will use SUMX to calculate the multiplied income:

Multiplied Income = 
    SUMX(
        SUMMARIZE('Table', 'Table'[Band], "Income", SUM('Table'[Income])),
        [Income] * SWITCH([Band],
            1, Parameter1[Parameter1 Value], 
            2, Parameter2[Parameter2 Value], 
            3, Parameter3[Parameter3 Value],
            1)
        )

The result will be as follows:

enter image description here

Upvotes: 1

Related Questions