Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
tex628
Community Champion
Community Champion

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
Super User

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

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
Community Champion
Community Champion

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

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

tex628
Community Champion
Community Champion

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.