Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
@Anonymous ,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
@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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |