Forum Discussion

michael_knight's avatar
michael_knight
Icon for Post Prodigy rankPost Prodigy
5 years ago
Solved

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?

 

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

 

Thanks,

Mike

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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.

    Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

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




     

     

     

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.

    Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

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




     

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Please provide what is your expected output? what output your are expecting from new measure ?

    • michael_knight's avatar
      michael_knight
      Icon for Post Prodigy rankPost Prodigy
      5 years ago

      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 

  • michael_knight's avatar
    michael_knight
    Icon for Post Prodigy rankPost Prodigy
    5 years ago

    Hi,

     

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

     

    Thanks