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
nele
Frequent Visitor

Distinctcount filtered by measure

Hi,

 

I want to use the following expression:

Partners pos Delta = CALCULATE(DISTINCTCOUNT('Store Performance'[Customer Partner Name]),FILTER('Store Performance',[salesDelta]>0))

However, the "Partners pos Delta"-measure doesn't give the correct result. In the example below I have 1 out of 3 stores with a positive sales delta. So I want to count 1 row. The "Partners pos Delta"-measure gives me 3. I think this is because the [salesDelta] is not calcultated correctly. The [salesDelta] is another measure that I created before. It is our sales evolution (%) minus the sales evolution of the category (%). This is correctly calcultated per store (as can be seen in the table below), but when I want to count the stores with a positive salesDelta, I think my DAX expression doesn't take into account that it needs to calculate the salesDelta per store and then needs to count the stores with a positive one.
 
nele_0-1632390952821.png

 

I hope someone can help me with this problem. Thank you in advance!

1 ACCEPTED SOLUTION

@nele Try:

Partners pos Data = 
  VAR __Table =
    FILTER(
      ADDCOLUMNS(
        SUMMARIZE('Store Performance',[Customer Partner Name]),
        "__salesDelta",[salesDelta]
      ),
      [__salesDelta]>0
    )
RETURN
  COUNTROWS(
    DISTINCT(
      SELECTCOLUMNS(__Table,"Customer Partner Name",[Customer Partner Name])
    )
  )

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

7 REPLIES 7
Greg_Deckler
Super User
Super User

@nele Try:

Partners pos Data = 
  VAR __Table =
    FILTER(
      ADDCOLUMNS(
        ALLSELECTED('Store Performance),
        "__salesDelta",[salesDelta]
      ),
      [__salesDelta]>0
    )
RETURN
  COUNTROWS(
    DISTINCT(
      SELECTCOLUMNS(__Table,"Customer Partner Name",[Customer Partner Name])
    )
  )
     

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler , maybe I need to add that the [salesDelta] is calculated per "Customer Partner Name", but this is not based on 1 row. There are multiple rows for 1 partner and based on the sum of these rows, the [salesDelta] is calculted. I think this is the reason that I cannot just add a column "salesDelta" in my dataset, since the average of the individual salesDelta's is not the same as the salesDelta of the sum of the individual rows.

@Greg_Deckler , Thank you for your quick response! Unfortunately, it still gives me the same outcome.

 

nele_0-1632392430339.png

 

@nele What's the formula for salesDelta? Sample data would probably help as well. Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler , attached you can find sample data.

 

These are the steps that I do, using DAX expressions:

1. 

salesevoCat = (sum('Store Performance'[This Year - Sales Category])-SUM('Store Performance'[Last Year - Sales Category]))/SUM('Store Performance'[Last Year - Sales Category])
 
2. 
salesevoOwn = (sum('Store Performance'[This Year - Our Sales])-SUM('Store Performance'[Last Year - Our Sales]))/SUM('Store Performance'[Last Year - Our Sales])
 
3. salesDelta = [salesevoOwn]-[salesevoCat]
 
My goal now is to count the stores with a positive salesDelta. Like in the picture below. 
 
nele_1-1632393509046.png
CategoryCustomer Partner NameLast Year - Sales CategoryLast Year - Our SalesPeriodThis Year - Sales CategoryThis Year - Our Sales
CHOCCRF HYPER 458 KURINGEN8140081002021P01866009900
CHOCCRF HYPER 458 KURINGEN10300090002021P0210510010100
CHOCCRF HYPER 458 KURINGEN137100116002021P0314100012000
CHOCCRF HYPER 458 KURINGEN161500139002021P0413220014300
CHOCCRF HYPER 458 KURINGEN118700124002021P05957009000
CHOCCRF HYPER 458 KURINGEN104400109002021P06911008500
CHOCCRF HYPER 458 KURINGEN10140098002021P07910009400
CHOCCRF HYPER 458 KURINGEN9100082002021P08875008000
CHOCCRF HYPER 458 KURINGEN99000101002021P09830006700
FOODCRF HYPER 458 KURINGEN4290080002021P01483006700
FOODCRF HYPER 458 KURINGEN5220084002021P02507009200
FOODCRF HYPER 458 KURINGEN87900130002021P03456005900
FOODCRF HYPER 458 KURINGEN5030076002021P04452006100
FOODCRF HYPER 458 KURINGEN5380078002021P05456006900
FOODCRF HYPER 458 KURINGEN5110064002021P06382005500
FOODCRF HYPER 458 KURINGEN4890076002021P07421007400
FOODCRF HYPER 458 KURINGEN4390059002021P08453007100
FOODCRF HYPER 458 KURINGEN3380052002021P09300004000
PETFOODCRF HYPER 458 KURINGEN33400140002021P013510013000
PETFOODCRF HYPER 458 KURINGEN35600135002021P023500012600
PETFOODCRF HYPER 458 KURINGEN38500166002021P033220011900
PETFOODCRF HYPER 458 KURINGEN31600135002021P043230011200
PETFOODCRF HYPER 458 KURINGEN33900150002021P053290011700
PETFOODCRF HYPER 458 KURINGEN35900141002021P062970011200
PETFOODCRF HYPER 458 KURINGEN34400133002021P073420011900
PETFOODCRF HYPER 458 KURINGEN29900122002021P083530011800
PETFOODCRF HYPER 458 KURINGEN33000131002021P093170011500

@nele Try:

Partners pos Data = 
  VAR __Table =
    FILTER(
      ADDCOLUMNS(
        SUMMARIZE('Store Performance',[Customer Partner Name]),
        "__salesDelta",[salesDelta]
      ),
      [__salesDelta]>0
    )
RETURN
  COUNTROWS(
    DISTINCT(
      SELECTCOLUMNS(__Table,"Customer Partner Name",[Customer Partner Name])
    )
  )

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler , this works for 100%! Thank you so much!

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.

Top Solution Authors