Distinctcount filtered by measure

[nele]

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.

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

[Greg_Deckler]

@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])
    )
  )
     

[nele]

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

[nele]

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

[Greg_Deckler]

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

[nele]

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

Category	Customer Partner Name	Last Year - Sales Category	Last Year - Our Sales	Period	This Year - Sales Category	This Year - Our Sales
CHOC	CRF HYPER 458 KURINGEN	81400	8100	2021P01	86600	9900
CHOC	CRF HYPER 458 KURINGEN	103000	9000	2021P02	105100	10100
CHOC	CRF HYPER 458 KURINGEN	137100	11600	2021P03	141000	12000
CHOC	CRF HYPER 458 KURINGEN	161500	13900	2021P04	132200	14300
CHOC	CRF HYPER 458 KURINGEN	118700	12400	2021P05	95700	9000
CHOC	CRF HYPER 458 KURINGEN	104400	10900	2021P06	91100	8500
CHOC	CRF HYPER 458 KURINGEN	101400	9800	2021P07	91000	9400
CHOC	CRF HYPER 458 KURINGEN	91000	8200	2021P08	87500	8000
CHOC	CRF HYPER 458 KURINGEN	99000	10100	2021P09	83000	6700
FOOD	CRF HYPER 458 KURINGEN	42900	8000	2021P01	48300	6700
FOOD	CRF HYPER 458 KURINGEN	52200	8400	2021P02	50700	9200
FOOD	CRF HYPER 458 KURINGEN	87900	13000	2021P03	45600	5900
FOOD	CRF HYPER 458 KURINGEN	50300	7600	2021P04	45200	6100
FOOD	CRF HYPER 458 KURINGEN	53800	7800	2021P05	45600	6900
FOOD	CRF HYPER 458 KURINGEN	51100	6400	2021P06	38200	5500
FOOD	CRF HYPER 458 KURINGEN	48900	7600	2021P07	42100	7400
FOOD	CRF HYPER 458 KURINGEN	43900	5900	2021P08	45300	7100
FOOD	CRF HYPER 458 KURINGEN	33800	5200	2021P09	30000	4000
PETFOOD	CRF HYPER 458 KURINGEN	33400	14000	2021P01	35100	13000
PETFOOD	CRF HYPER 458 KURINGEN	35600	13500	2021P02	35000	12600
PETFOOD	CRF HYPER 458 KURINGEN	38500	16600	2021P03	32200	11900
PETFOOD	CRF HYPER 458 KURINGEN	31600	13500	2021P04	32300	11200
PETFOOD	CRF HYPER 458 KURINGEN	33900	15000	2021P05	32900	11700
PETFOOD	CRF HYPER 458 KURINGEN	35900	14100	2021P06	29700	11200
PETFOOD	CRF HYPER 458 KURINGEN	34400	13300	2021P07	34200	11900
PETFOOD	CRF HYPER 458 KURINGEN	29900	12200	2021P08	35300	11800
PETFOOD	CRF HYPER 458 KURINGEN	33000	13100	2021P09	31700	11500

[Greg_Deckler · 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])
    )
  )

[nele]

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