Need help with Dynamic table bases on the selection on the Slicer

Power BI
1

Hello Experts,

I am an intern at Retail chain and this is my second project and really need your help!

In my powerBI, there are two tables with no direct relationship.
The first table is called retailers: with the following column:
Retailer
Amazon
Walmart
HD
Target

The Second table is called Sale Price: with the following Columns:

Item Price
ABC1 6
ABC2 17
ABC3 8
ABC4 17
ABC5 15
ABC6 14
ABC7 16
ABC8 19
ABC9 25

The BI will have a slicer with the selection of the retailers. And depend on which selection then it will apply the discount on the sale price.
I created two new measures on the Sale Price table as below.
1st measure:
Margin = SWITCH(
SELECTEDVALUE(ā€˜Discountā€™[Retailer]),
ā€œAmazonā€, 0.40,
ā€œHDā€, 0.45,
ā€œTargetā€, 0.50,
ā€œWalmartā€, 0.55,
BLANK())
2nd measure:
Discount = ā€˜Sale Priceā€™[Price]*ā€˜Sale Priceā€™[Margin]

However, my Discount column is coming back with blank with no value.
Could anyone please advise what i am missing?
Thank you for your help!
Sale Discount.pbix (31.7 KB)

2

Hi,

I see a few issues with your approach here.
You are trying to create a calculated column based on the slicer.
and there is no relationship between the two tables

Following is the solution. I have created for you.
I have created 2 new tables.

  1. Retailers this is just the names of the retailers
  2. New Sales this is a table I have created as a cross join between your original sales price and discount tables

I have created a relationship between the two tables.

Followed by two measures.
discount applicable and Net Price

.
Sale Discount_sol_jss.pbix (38.5 KB)

Hope this helps
:slight_smile:

1 Like
3

Thank you for getting back to me!
How can I apply a logic to when only the user select the retailer in the slicer then the discount will apply. If they dont select anything then the discount will be at $0.

thank again!

4

okay, I thought over it again and here is the solution if I understood your requirement correctly this time :

When no selection:

image
image699Ɨ622 15.5 KB

When selection is made

image
image715Ɨ640 22.4 KB

here is the key measure you can use:

PriceAfterDiscount =

VAR _percentage = SWITCH(TRUE(),

SELECTEDVALUE(Retailers[Retailer]) = "Amazon",0.4,

SELECTEDVALUE(Retailers[Retailer]) = "Walmart",0.5,

SELECTEDVALUE(Retailers[Retailer]) = "HD",0.55,

SELECTEDVALUE(Retailers[Retailer]) = "Target",0.45,

1)

VAR _price = SUM('New Sales'[Price])

VAR _sol = _percentage*_price

return

_sol

here is the PBX File forreference
Sale Discount_sol_jss.pbix (39.3 KB)

Hope this helps.

1 Like