cancel
Showing results for
Did you mean:
Post Prodigy

## Measure Formula changes depending on Slicer option

Hi,

I'm trying to create a measure which will change the formula depending on who is selected on the Slicer

We have multiple agents that get a different % of the Margin. In the example below I have two agents and a measure for each agent with the Margin % they get. I want to have one measure which will change depending on who is selected in the slicer

John gets 1% and Tom gets 3%. I want to consolidate this into one measure

I've currently got the Agents in a different table as it's part of a Star Scheme in the main report, as well as a date table which as inactive relationships as I use the USERELATIONSHIP function in my measures.

Does anyone know how I can do this?

Thanks,

Mike

1 ACCEPTED SOLUTION
Resolver II

Create a new table that will store margin values for each agent.

Check and de-activate the relation between Agents and margin.

Create new measure

``New Margin % = (AVERAGE(margin[margin]))/100 * [Margin Measure] ``

Now if you don't select any agent then it will select average margin.

Now create a new slicer from Agent from the margin table.
Output for John :

Output for Tom :

Thank you.

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

5 REPLIES 5
Resolver II

Create a new table that will store margin values for each agent.

Check and de-activate the relation between Agents and margin.

Create new measure

``New Margin % = (AVERAGE(margin[margin]))/100 * [Margin Measure] ``

Now if you don't select any agent then it will select average margin.

Now create a new slicer from Agent from the margin table.
Output for John :

Output for Tom :

Thank you.

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

Post Prodigy

Thank you @MrSujay

Post Prodigy

Hi,

Still looking to solve this issue. If anyone has any suggestions please let me know

Thanks

Resolver II

Post Prodigy

My expected outcome is a single measure with a formula that changes depending on who is selected on the slicer

Currently there is two measures:

``John Margin % = (1/100) * [Margin Measure] ``

``Tom Margin % = (3/100) * [Margin Measure] ``

And I want them to be consolidated into one measure

Announcements

#### 2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.