cancel
Showing results for
Did you mean:
Regular Visitor

## How to get the % of all but not the selectedvalue

Hi,

Im working on a dashboard, and there is a KPI in % that needs to visualize the percentage of the customers overall (all shops, but not the selected shop).

So when I select a Shop, I wanne see the % of the others they doing against the shop that I selected. I was hoping there is something in DAX that you can tell that the selected shop must not be calculated but only the shops that not selected.:

I have a DAX that gets all the shops with the customers of this year VS prev year and there I get a number:

% Customers All Shops (ex selected Shop (currentYear VS PrevYear) =

VAR _currentALL = CALCULATE([Customer],ALL('Shop'))
VAR _lastyearALL = CALCULATE([Customer PY DoW],ALL('Shop'))

RETURN

DIVIDE((_currentALL -_lastyearALL), _lastyearALL, 0)

Overall Customer of All Shops (Ex Shop selected)

Can somebody help me?

Gr, GG

1 ACCEPTED SOLUTION
Super User II

Hello @KB_BI_GG,

Anything on the line of this maybe?

``````% Customers All Shops (ex selected Shop) =
VAR SelectedVal = SELECTEDVALUE('Shop'[Customer])
VAR Remaining_ = CALCULATE([Customer],ALL('Shop') , 'Shop'[Customer] <> SelectedVal)
RETURN
DIVIDE(([Customer] -_Remaining), _Remaining, 0)``````

Br
J

8 REPLIES 8

Thx for the solution 🙂

Regular Visitor

Hi @v-jingzhang

I have solved my problem with the following statement:

First I calculate the Customer Current Year:

Customer (Prop-Country) =

VAR SelectedProperty = SELECTEDVALUE('Shop'[Shop Name])
VAR Remaining_ = CALCULATE([Visitors],ALL('Shop'[Shop Name]),ALL('Country'[Country Name]))

RETURN
Remaining_

Second I calculate the Customer Prev Year:

Customer py (Prop-Country) =
VAR Remaining_ = CALCULATE([Customer PY DoW],ALL('Shop'[Shop Name]),ALL('Country'[Country Name]))

RETURN
Remaining_

Calculate:
% Visitors All Property =
VAR Remaining_ = [Customer (Prop-Country)]
VAR RemainingPrev_ = [Customer py (Prop-Country)]

RETURN
DIVIDE((Remaining_ - RemainingPrev_), RemainingPrev_, 0)

Is this a good way or Am I doing to much for now??

Super User IV

@KB_BI_GG ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Proud to be a Super User!

Regular Visitor

Well that's not easy because it use a live Connection to a tabular model. Do you want it in Table level?

I can try to make a example one, but do you understand the CASE?

Greetz.

Super User II

Hello @KB_BI_GG,

Anything on the line of this maybe?

``````% Customers All Shops (ex selected Shop) =
VAR SelectedVal = SELECTEDVALUE('Shop'[Customer])
VAR Remaining_ = CALCULATE([Customer],ALL('Shop') , 'Shop'[Customer] <> SelectedVal)
RETURN
DIVIDE(([Customer] -_Remaining), _Remaining, 0)``````

Br
J

Regular Visitor

Hi J,

I have trying you're solution and get some changes in values. But im not sure if the calculation is right. I have some background information:

The model is a STARMODEL (Dim_shops, Fact_CountCustomer). The column im using is:

Dim_Shop(Property_Name)

Fact_CountCustomer([Customer],[Customer PY DoW])   (CurrentYear VS Prev Year)

Dim_date(Date)

Between the table is a relation:

So in the <> filter is the Shop only the Property_Name.

``'Shop'[Customer] <> SelectedVal``

Community Support

Has the problem been solved? If so, you may accept the appropriate post as the solution or post your own workaround as a solution to help other members find it quickly. Thanks.

Best Regards,
Community Support Team _ Jing Zhang

Super User II

If Property_Name is the column you're slicing on it should look like this instead:

``````% Customers All Shops (ex selected Shop) =
VAR SelectedVal = SELECTEDVALUE('Shop'[Property_Name])
VAR Remaining_ = CALCULATE([Customer],ALL('Shop') , 'Shop'[Property_Name] <> SelectedVal)
RETURN
DIVIDE(([Customer] -_Remaining), _Remaining, 0)``````

[Customer] Should return the value for the selected shop.
_Remaining Should return the value for all other shops.

Let me know how it goes.

/ J

Announcements