Forum Discussion

nele's avatar
nele
Frequent Visitor
4 years ago
Solved

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 ...
  • Greg_Deckler's avatar
    Greg_Deckler
    4 years ago

    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])
    )
  )
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
4 years ago

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's avatar
nele
Frequent Visitor
4 years ago

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. 
 
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
  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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's avatar
      nele
      Frequent Visitor
      4 years ago

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