Forum Discussion
Distinctcount filtered by measure
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]) ) )
Community Championnele 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])
)
)
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.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
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]) ) )