Forum Discussion

AliceW's avatar
AliceW
Icon for Power Participant rankPower Participant
3 years ago
Solved

Total with 'Yes' in Column A while ignoring column B

This seems so simple, yet it's so frustrating. Any help would be welcome, please.

So, I have this table, 'Sales'. For simplicity' sake, it has three columns:

- Account

- Has Apples: Y/N

- Has Oranges: Y/N

One Account can have Apples, Orange, both, or none.

AccountHas ApplesHas Oranges
ANN
BYY
CYN
DNY
eNY

 

To calculate the number of Accounts with oranges, easy, I did get the right total of 3 using this formula:

= Calculate(DistinctCount[Account], Sales[Has Oranges] = "Y")

 

However, and this is where my problem lies, I want to also show this total of 3 when the 'Has Apples' filter is in the page set to 'Y'.

I tried REMOVEFILTERS(Has Apples). I just got a smaller number, 2, so the difference between one filter and the other. I tried ALL(Has Apples). Nothing.

 

If I put the top formula in a card and just disconnect it to the 'Has Apples' filter, I get 3. But I really need it in a table that is affected by it (the end result is a %)...

Need Help

7 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    3 years ago

    AliceW It is possible that you may run into an issue with Auto exist but try this:

    Measure =
  COUNTROWS(
    DISTINCT(
      SELECTCOLUMNS(
        FILTER( ALL('Table'), [Has Oranges] = "Y" ),
        "__Account",
        [Account]
      )
    )
  )
      • Greg_Deckler's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        3 years ago

        AliceW So I mocked this up and it seems to work OK but I'm guessing there are additional things going on that may impact the solution. See attached PBIX below signature.

  • Syk's avatar
    Syk
    Icon for Resident Rockstar rankResident Rockstar
    3 years ago
    Did you try leaving your remove filters blank?

    Accounts with oranges =     Calculate(DistinctCount(Sales[Account]), Sales[Has Oranges] = "Y",REMOVEFILTERS())
    • AliceW's avatar
      AliceW
      Icon for Power Participant rankPower Participant
      3 years ago

      It's crazy, I just get blanks. If I use ALL, it DOES work, but I have other columns in this table I need to use.