cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KB_BI_GG
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)Overall Customer of All Shops (Ex Shop selected)


Can somebody help me?

Gr, GG

1 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


Connect on LinkedIn

View solution in original post

8 REPLIES 8
Syndicate_Admin
Administrator
Administrator

Thx for the solution 🙂

KB_BI_GG
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??
 
amitchandak
Super User IV
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.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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.

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


Connect on LinkedIn

View solution in original post

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

 

@KB_BI_GG 

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

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


Connect on LinkedIn

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.