How to adjust commission tier with slicer?

[Black_magic100]

I have a fact table with transactions.  If somebody buys a package with a specific ID I have a calculated column using DAX to add in the appropriate commission amount.

Right now I have 3 columns, Tier 1, Tier 2, Tier 3 commissions all in my fact table, but the issue is I can not filter by those values obviously.  

I would like to have it so that I can use a button or slicer to select Tier 2 and Tier 3 commissions.  I can not simply multiply the values as the commissions tiers are not all equal increments.  

There is also a lookup table for packageIDs and I was going to have the commission in that table, but when I went to sum the values it was summing it up for just that table and not referencing the respective packages that were sold.  

[Greg_Deckler]

I would recommend using a disconnected table that just has a single column and lists Tier1, Tier2, Tier3. Use that as your slicer. Then, you could have a measure that does a MAX or SELECTEDVALUE for that table/column and perform the correct calculation based upon the value (in other words, use your Tier1 column, Tier2 column, etc. from your fact table. This is called the "disconnected table trick". See this article: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick...

[Black_magic100]

I feel like I am trying to do something like this:

Tier 2 = SELECTEDVALUE('Commission'[CommissionTier], SUM(ContractPurchases[CommissionsTier2]))

Except of course this formula does not work as it just returns the String of the tier selected when I select the filter.  So in the filter if I choose Tier 1 (disconnected table) it just shows the text "Tier 1".  If I get rid of the filter and use the new measure as a card I DO get the correct summed value.  I could add 2 more cards and hide values based on filter selections?  However, I am not sure if this would work for my bar graph, right?  Also, bookmarks are not an option as it would reset other filters on the page.

[v-cherch-msft · Accepted solution]

Hi @Black_magic100

You may try to create a measure like below.Here are some posts for your reference.If it is not your case,it's better that if you could share your sample data which could reproduce your scenario and your desired output.

https://community.powerbi.com/t5/Desktop/What-if-calculation-based-on-selected-value-in-slicer-not/m...

https://community.powerbi.com/t5/Desktop/Change-calculation-of-a-column-based-on-a-slicer/m-p/563080...

Tier 2 =
VAR a =
    SELECTEDVALUE ( 'Commission'[CommissionTier] )
RETURN
    CALCULATE (
        SUM ( ContractPurchases[CommissionsTier2] ),
        FILTER ( ContractPurchases, ContractPurchases[CommissionTier] = a )
    )

Regards,

Cherie

[Black_magic100]

I already have a disconnected table, but I wasn't sure what to do next.  I've only ever used SELECTEDVALUE for adjusting text dynamically.  How can I use the disconnected table and filter out the visual based on Tier 1, 2, or 3.  At any given time I only want one tier to be shown at any given time.