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
Anonymous
Not applicable

Creating a tag based on DAX outcome

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 

MonthCustomerProductSalesTarget
1-Jan-17Customer001boat101100
1-Feb-17Customer001boat104100
1-Mar-17Customer001boat99100
1-Apr-17Customer001boat90100
1-Jan-17Customer001car101100
1-Feb-17Customer001car98100
1-Mar-17Customer001car87100
1-Apr-17Customer001car110100
1-Jan-17Customer002boat97100
1-Feb-17Customer002boat85100
1-Mar-17Customer002boat77100
1-Apr-17Customer002boat98100
1-Jan-17Customer002car68100
1-Feb-17Customer002car99100
1-Mar-17Customer002car102100
1-Apr-17Customer002car140100
1-Jan-17Customer003boat120100
1-Feb-17Customer003boat100100
1-Mar-17Customer003boat125100
1-Apr-17Customer003boat87100
1-Jan-17Customer003car120100
1-Feb-17Customer003car130100
1-Mar-17Customer003car120100
1-Apr-17Customer003car110100
1-Jan-17Customer004boat98100
1-Feb-17Customer004boat88100
1-Mar-17Customer004boat135100
1-Apr-17Customer004boat98100
1-Jan-17Customer004car100100
1-Feb-17Customer004car89100
1-Mar-17Customer004car108100
1-Apr-17Customer004car117100
6 REPLIES 6
v-sihou-msft
Employee
Employee

@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,

 

Anonymous
Not applicable

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)

Capture.JPGHere attached is the results on 3 scenrios...

 

  

vanessafvg
Super User
Super User

@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?





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




CahabaData
Memorable Member
Memorable Member

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? .....

www.CahabaData.com
Anonymous
Not applicable

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.. Capture.JPG

Anonymous
Not applicable

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.

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.