How to get the % of all but not the selectedvalue

[Anonymous]

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

[Syndicate_Admin]

Thx for the solution 🙂

[Anonymous]

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??

[amitchandak]

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

[Anonymous]

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.

[tex628 · Accepted solution]

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

[Anonymous]

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

[v-jingzhang]

@Anonymous 

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

[tex628]

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