Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi PBI , I need to work out what Percentage based on Count of Customers achieve their target. I have attached a basic data which I hope you can assist.
In Summary, the end result I am looking for
1) "% of Customer" Achieve their target
2) "% of Customer" Achieve their target when I apply the Product Filter (boat or cars)
I am trying to get this "% of Customer" on a Circular Visuals
I tried IF, SUMX etc New Column, New Measure and did not work 😞
Many Thanks ...
I thought I created this data for you
Month | Customer | Product | Sales | Target |
1-Jan-17 | Customer001 | boat | 101 | 100 |
1-Feb-17 | Customer001 | boat | 104 | 100 |
1-Mar-17 | Customer001 | boat | 99 | 100 |
1-Apr-17 | Customer001 | boat | 90 | 100 |
1-Jan-17 | Customer001 | car | 101 | 100 |
1-Feb-17 | Customer001 | car | 98 | 100 |
1-Mar-17 | Customer001 | car | 87 | 100 |
1-Apr-17 | Customer001 | car | 110 | 100 |
1-Jan-17 | Customer002 | boat | 97 | 100 |
1-Feb-17 | Customer002 | boat | 85 | 100 |
1-Mar-17 | Customer002 | boat | 77 | 100 |
1-Apr-17 | Customer002 | boat | 98 | 100 |
1-Jan-17 | Customer002 | car | 68 | 100 |
1-Feb-17 | Customer002 | car | 99 | 100 |
1-Mar-17 | Customer002 | car | 102 | 100 |
1-Apr-17 | Customer002 | car | 140 | 100 |
1-Jan-17 | Customer003 | boat | 120 | 100 |
1-Feb-17 | Customer003 | boat | 100 | 100 |
1-Mar-17 | Customer003 | boat | 125 | 100 |
1-Apr-17 | Customer003 | boat | 87 | 100 |
1-Jan-17 | Customer003 | car | 120 | 100 |
1-Feb-17 | Customer003 | car | 130 | 100 |
1-Mar-17 | Customer003 | car | 120 | 100 |
1-Apr-17 | Customer003 | car | 110 | 100 |
1-Jan-17 | Customer004 | boat | 98 | 100 |
1-Feb-17 | Customer004 | boat | 88 | 100 |
1-Mar-17 | Customer004 | boat | 135 | 100 |
1-Apr-17 | Customer004 | boat | 98 | 100 |
1-Jan-17 | Customer004 | car | 100 | 100 |
1-Feb-17 | Customer004 | car | 89 | 100 |
1-Mar-17 | Customer004 | car | 108 | 100 |
1-Apr-17 | Customer004 | car | 117 | 100 |
@Anonymous
Does this "% of Customer achieve target" need to be analyzed on month level?
If yes, you can add a column to tag if a customer is satisfiled per month per product.
If you need to analyze covering multiple products or multiple months, what's your logic to determine a Customer is satisfied? Does this customer must achieve target each month each product? Or just total sales greater than target is ok?
For this scenario, you should use ALLEXCEPT to have your calculation group on Customer and Product.
Please share some expected result to clarify your requirement.
Regards,
Hi simon
Simon: Does this "% of Customer achieve target" need to be analyzed on month level?If yes, you can add a column to tag if a customer is satisfiled per month per product.
Malcolm: Yes. Will now tag
simon:If you need to analyze covering multiple products or multiple months, what's your logic to determine a Customer is satisfied?
Malcolm: the SalesDirector, wil be slicing & dicing by product, by customer, by Month - there is no limit
simon : Does this customer must achieve target each month each product?
Malcolm: No
Simn: Or just total sales greater than target is ok?
Malcolm: Sales Greater than Tgt is OK .. Notice in Scenario 1, 50% of Customers (2 out of 4) achieve the Tgt (neither mth & product were selected in scenario#1)
Here attached is the results on 3 scenrios...
@Anonymous
not 100% sure of what you are asking..
new measure
"% of Customer" Achieve their target =
DIVIDE (
CALCULATE ( DISTINCTCOUNT ( [customer] ), sales >= target ),
CALCULATE ( DISTINCTCOUNT ( [customer] ), ALL ( customer ) )
are you asking to do a unique count of customers or rows?
Proud to be a Super User!
to clarify your post - using Customer002 in example: there are 8 records and 2 are over the target. So their overall is 2/8 or 25%.
by category it is 0% for boat and 50% for car.
is this what you seek? .....
Hi emudria, I manage to create a flag "Achieved" but The DAX does not seem to count correctly.. Some 😞
Hi Vanessafvg, I am trying out yr DAX. the example I gave is a simplified.. The real data I have consist of 14711 discount customers, Unfortunately I could not get the to count correctly.. I am playing with it for 24hrs.. and wil lcome back if I am really stuck.... Stay tune
Hi Cahabadata, Please refer Image I attached on 3 scenarios..
May be put a flag with a simple check if the actual is over the target or not. then you can count the number of "achieved" as percentage of total. This should also work for any additional categories.
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 |
---|---|
109 | |
101 | |
84 | |
79 | |
69 |
User | Count |
---|---|
120 | |
110 | |
95 | |
82 | |
77 |