cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Anonymous
Not applicable

## visual slicer and calculate filter on the same column

Hi all,

Baground:

Comparing one organisations pricing to everyone else in the market. Using two simple measures, one calcuates the \$ difference the other calcuates the % change. See below for % change measure, with difference being built the same:

Spoiler
```% Change =
VAR __Company = CALCULATE([Average],MarketPricingTable[Organisation] = "Disney")
VAR __Competitor = CALCULATE([Average],,MarketPricingTable[Organisation] <> "Disney")
RETURN DIVIDE(__Competitor - __Company ,__Company ,0)```

Problem:

Visuals do not change with a slicer using Organisation:

Finer details:

The difference measure has 1 card and 2 bar graphs.  One of the bar graphs does change when using the Organisation slicer.

The % change measure has 1 car and three line graphs. 2 of the line graphs change when disney and other companies are selected in the slicer.  < miss viewed this as the graphs were small.

Solution:

closest link online I could find was: https://exceleratorbi.com.au/filtering-column-via-visual-calculate/

The company's cacluate needs to use all finters/slicers except for Organisation.  Where as Competitor needs all filters/slicers.

Edit: removed a miscellaneous bit.

Edit 2: strikethough txt.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Solution:

HA, got it:

```% Change =
VAR __company = CALCULATE(AVERAGE(Sheet1[Price]),FILTER(ALL(Sheet1[Organisation]),Sheet1[Organisation] = "DD"))
VAR __competitor = CALCULATE([Average],FILTER(ALLSELECTED(Sheet1[Organisation]),Sheet1[Organisation] <> "DD"))
RETURN DIVIDE(__competitor - __company,__company,0)```
5 REPLIES 5
Anonymous
Not applicable

Solution:

HA, got it:

```% Change =
VAR __company = CALCULATE(AVERAGE(Sheet1[Price]),FILTER(ALL(Sheet1[Organisation]),Sheet1[Organisation] = "DD"))
VAR __competitor = CALCULATE([Average],FILTER(ALLSELECTED(Sheet1[Organisation]),Sheet1[Organisation] <> "DD"))
RETURN DIVIDE(__competitor - __company,__company,0)```
Community Support

Hi AaronSC,

Have you change the interactions between visuals?

In addtion, it seems like your measure % Change doesn't have interactions with your slicers, have you tried ALLSELECTED()? If this issue persists, could you please post some sample data or make a mock-up for further analysis?

Regards,

Jimmy Tao

Anonymous
Not applicable

Hi, here is a pbix file. I was able to duplicate the issue with % change and Diff.  However, I could not find a way to make it only work for some of the diff bar graphs.

If there is a better way to share on this forum, please le\t me know.

Community Support

Hi AaronSC,

Modify the measure using DAX formula below and check if it can work:

```% Change =
VAR __company = CALCULATE([Average],FILTER(ALLSELECTED(Sheet1[Organisation]), Sheet1[Organisation] = "DD"))
VAR __competitor = CALCULATE([Average], FILTER(ALLSELECTED(Sheet1[Organisation]), Sheet1[Organisation] <> "DD"))
RETURN DIVIDE(__competitor - __company,__company,0)
```

Regards,

Jimmy Tao

Anonymous
Not applicable

I have not changed the interactions between visuals for any of the %/Diff visuals.

I have just tried:

```% Change =
VAR __Company = CALCULATE([Average],MarketPricingTable[Organisation] = "Disney", AllSELECTED(MarketPricingTable[Organisation]))
VAR __Competitor = CALCULATE([Average],MarketPricingTable[Organisation] <> "Disney")
RETURN DIVIDE(__Competitor - __Company ,__Company ,0)```

I will, see if I can get you a mockup/sample data.

Regards,

Aaron

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors