Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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)
Can somebody help me?
Gr, GG
Solved! Go to 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
Thx for the solution 🙂
Hi @v-jingzhang
I have solved my problem with the following statement:
First I calculate the Customer Current Year:
Second I calculate the Customer Prev Year:
@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.
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
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
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
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |