Selected Value issue

[PBIfanatic]

Hi,

I am trying to represent the below data

Category	Product	Sales
Cat1	Prod1	100
Cat1	Prod2	200
Cat2	Prod3
Cat2	Prod4

as this, with a conditional format based on the Sales Value. 

Product

Prod1

Prod2

Prod3

Prod4

The challenge I have is I need to show all the products irrespective of whether it is from Cat1 or 2. Category is the slicer. So I have used edit interactions to ignore the selection of the slicer. 

But the Sales is a measure derived from few other measures and the selection of the category. 

As I am ignoring the selection for the table I am displaying, it ignores the measure calculation which used SelectedValue. 

How do I get all the Products and use SelectedValue in the scenario I mentioned. 

Thanks in advance.

[amitchandak · Accepted solution]

@PBIfanatic , I think you need an independent category table and use it as per need

refer if needed

Need of an Independent Table in Power BI: https://youtu.be/lOEW-YUrAbE

[v-yalanwu-msft · Accepted solution]

Hi， @PBIfanatic ；

You could create a table as a slicer by cate column,then modify the measure as follow:

1.create a table as slicer.

slicer = VALUES('Table'[Category])

2.then modify measure.

Measure = IF(ISFILTERED(slicer[Category]), CALCULATE(SUM([Sales]),FILTER(ALL('Table'),[Category]=SELECTEDVALUE('slicer'[Category]))),SUM([Sales]))

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlexisOlson]

@PBIfanatic As @amitchandak points out, this isn't possible without a separate dimension table. Ideally, you'd have a dimension table for Category and another for Product.

I recommend this article for more detail on why star schemas with proper dimension tables are important:
https://www.sqlbi.com/articles/the-importance-of-star-schemas-in-power-bi/

[amitchandak · Accepted solution]

@PBIfanatic , I think you need an independent category table and use it as per need

refer if needed

Need of an Independent Table in Power BI: https://youtu.be/lOEW-YUrAbE

[PBIfanatic]

Thanks @amitchandak  this worked.

Thanks @v-yalanwu-msft ; @AlexisOlson for your replies