Filtering on a Card

[GregMorgan]

Hello,

I am trying to create a filter on a card that shows the total number of territories that have a variance that is greater than zero.  For example, suppose this is the list of territories and sales variances:

Territory 1       $+1,000

Territory 2       $+2,000

Territory 3       $-1,500

Territory 4       $-3,000

Territory 5       $+4,000

When I initially create the card and do a distinct count on the territories, it shows that I have 5 territories.  I would like to add a filter based on the variance so that the total only includes territories that have a variance >0 (in this case 3).  When I add the filter to the card, the options are greyed out so that I cannot filter.

Any ideas?

Thanks

Greg

[GregMorgan]

Thanks for your quick response!  Below is a screen show of the card and filters:

The card above is based on a distinct count of territories.  24 is the total, but I want to only show the number of territories that have a variance that is greater than zero.  But as you can see, the "$ Variance" filter is greyed out.

My data (in a table view) looks as follows, the data in this table is aggregated based on detailed rows.  (On the left are the territory names, I have cut off most of them).  I want the care to only show the total territories that have a "$ Variance" >0

Let me know if you need additional information.

Thanks

Greg

[GregMorgan]

My first screen shot did not post for some reason, please see below:

[vanessafvg]

that doesn't look greyed out to me have you tried to drop it down?  

you could however manage this with a dax calculation

Unique Territories with variance =
VAR varonly =
    IF ( SUM ( table[variance] ) > 0, 1, 0 )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( table[territory] ),
        FILTER ( table, variances = 1 )
    )

[GregMorgan]

Thanks. It is definitely greyed out (hard to see in the screen shot). For some reason, Power BI will not allow a filtering a value on a distinct count of another column. (I have seen other users comment onthis as well).

I tried your solution and set up a new measure as follows:

Unique Territories with variance =

VAR varonly =

   IF ( SUM ( 'SalesbyTerritory'[Variance] ) > 0, 1, 0 )

RETURN

   CALCULATE (

       DISTINCTCOUNT ( 'SalesbyTerritory'[Territory] ),

       FILTER ( SalesbyTerritory, SalesbyTerritory[Variance] = 1 )

   )

When I add this to my card, I just get '(Blank)'. I am sure I am doing something wrong, are you able to determine the issue based on the code above?

Thanks again!

[vanessafvg]

very hard to figure it out without seeing your data, can you provide a sample?

[GregMorgan]

Below is a sample of the raw data.  Basically, this is sales data for multiple periods over 2020 and 2021.  I want to show on a card how many distinct territories had a positive variance for any given 'Month' and 'Region' based on the 'Variance' column.

[vanessafvg]

you need to share it in text format... otherwise i can't use it to test my code.   

[GregMorgan]

OK, please see below (tab delimited).  Please let me know if this works.

Scenario               Week    Month  Quarter Territory              Measure              Location               Value    2020       2021                Region 1               LOB        Payer Type / LOB              Variance

2020       wk1        January 2020      Qtr 1      TERRITORY 1       Sales - Product 1               Domestic Locations         2546.81                2546.81                 -             Region 1               LOB 1     Sales - Product 1               -2546.81

2020       wk1        January 2020      Qtr 1      TERRITORY 1       Sales - Product 2               Domestic Locations         46.69     46.69                -             Region 1               LOB 1     Sales - Product 2               -46.69

2020       wk1        January 2020      Qtr 1      TERRITORY 1       Sales - Product 3               Domestic Locations         1059.25                1059.25                 -             Region 1               LOB 1     Sales - Product 3               -1059.25

2020       wk1        January 2020      Qtr 1      TERRITORY 2       Sales - Product 1               Domestic Locations         3491.62                3491.62                 -             Region 1               LOB 1     Sales - Product 1               -3491.62

2020       wk1        January 2020      Qtr 1      TERRITORY 2       Sales - Product 2               Domestic Locations         1067       1067                -             Region 1               LOB 1     Sales - Product 2               -1067

2020       wk1        January 2020      Qtr 1      TERRITORY 2       Sales - Product 4               Domestic Locations         27250.02                27250.02              -             Region 1               LOB 1     Sales - Product 4               -27250.02

2020       wk1        January 2020      Qtr 1      TERRITORY 2       Sales - Product 5               Domestic Locations         45000                45000    -             Region 1               LOB 1     Sales - Product 5               -45000

2020       wk1        January 2020      Qtr 1      TERRITORY 2       Sales - Product 3               Domestic Locations         15765.93                15765.93              -             Region 1               LOB 1     Sales - Product 3               -15765.93

2020       wk1        January 2020      Qtr 1      TERRITORY 3       Sales - Product 4               Domestic Locations         17852.23                17852.23              -             Region 1               LOB 1     Sales - Product 4               -17852.23

2020       wk1        January 2020      Qtr 1      TERRITORY 4       Sales - Product 1               Domestic Locations         10571.83                10571.83              -             Region 1               LOB 1     Sales - Product 1               -10571.83

2020       wk1        January 2020      Qtr 1      TERRITORY 4       Sales - Product 2               Domestic Locations         7744.11                7744.11                 -             Region 1               LOB 1     Sales - Product 2               -7744.11

2020       wk1        January 2020      Qtr 1      TERRITORY 4       Sales - Product 4               Domestic Locations         23796.99                23796.99              -             Region 1               LOB 1     Sales - Product 4               -23796.99

2020       wk1        January 2020      Qtr 1      TERRITORY 4       Sales - Product 3               Domestic Locations         7178.76                7178.76                 -             Region 1               LOB 1     Sales - Product 3               -7178.76

2020       wk1        January 2020      Qtr 1      TERRITORY 5       Sales - Product 1               Domestic Locations         19257.95                19257.95              -             Region 1               LOB 1     Sales - Product 1               -19257.95

2020       wk1        January 2020      Qtr 1      TERRITORY 5       Sales - Product 2               Domestic Locations         33795.43                33795.43              -             Region 1               LOB 1     Sales - Product 2               -33795.43

2020       wk1        January 2020      Qtr 1      TERRITORY 5       Sales - Product 4               Domestic Locations         76692.02                76692.02              -             Region 1               LOB 1     Sales - Product 4               -76692.02

2020       wk1        January 2020      Qtr 1      TERRITORY 5       Sales - Product 3               Domestic Locations         66034.39                66034.39              -             Region 1               LOB 1     Sales - Product 3               -66034.39

2020       wk1        January 2020      Qtr 1      TERRITORY 6       Sales - Product 1               Domestic Locations         17577.19                17577.19              -             Region 1               LOB 1     Sales - Product 1               -17577.19

2020       wk1        January 2020      Qtr 1      TERRITORY 6       Sales - Product 2               Domestic Locations         1902.5                1902.5   -             Region 1               LOB 1     Sales - Product 2               -1902.5

2020       wk1        January 2020      Qtr 1      TERRITORY 6       Sales - Product 4               Domestic Locations         85918.17                85918.17              -             Region 1               LOB 1     Sales - Product 4               -85918.17

2020       wk1        January 2020      Qtr 1      TERRITORY 6       Sales - Product 3               Domestic Locations         17779.72                17779.72              -             Region 1               LOB 1     Sales - Product 3               -17779.72

2020       wk1        January 2020      Qtr 1      TERRITORY 7       Sales - Product 1               Domestic Locations         49087.72                49087.72              -           Region 1               LOB 1     Sales - Product 1               -49087.72

2020       wk1        January 2020      Qtr 1      TERRITORY 7       Sales - Product 2               Domestic Locations         1233.18                1233.18                 -             Region 1               LOB 1     Sales - Product 2               -1233.18

2020       wk1        January 2020      Qtr 1      TERRITORY 7       Sales - Product 4               Domestic Locations         43640.67                43640.67              -             Region 1               LOB 1     Sales - Product 4               -43640.67

2020       wk1        January 2020      Qtr 1      TERRITORY 7       Sales - Product 5               Domestic Locations         67057.19                67057.19              -             Region 1               LOB 1     Sales - Product 5               -67057.19

2020       wk1        January 2020      Qtr 1      TERRITORY 7       Sales - Product 3               Domestic Locations         -17372.45                -17372.45             -             Region 1               LOB 1     Sales - Product 3               17372.45

2020       wk1        January 2020      Qtr 1      TERRITORY 8       Sales - Product 3               Domestic Locations         -25.68    -25.68     -             Region 1               LOB 1     Sales - Product 3               25.68

2020       wk1        January 2020      Qtr 1      TERRITORY 9       Sales - Product 1               Domestic Locations         6580.5                6580.5   -             Region 1               LOB 1     Sales - Product 1               -6580.5

2020       wk1        January 2020      Qtr 1      TERRITORY 9       Sales - Product 4               Domestic Locations         41041.35                41041.35              -             Region 1               LOB 1     Sales - Product 4               -41041.35

2020       wk1        January 2020      Qtr 1      TERRITORY 9       Sales - Product 3               Domestic Locations         40119.79                40119.79              -             Region 1               LOB 1     Sales - Product 3               -40119.79

2020       wk1        January 2020      Qtr 1      TERRITORY 10    Sales - Product 2               Domestic Locations         1899.23                1899.23                 -             Region 1               LOB 1     Sales - Product 2               -1899.23

2020       wk1        January 2020      Qtr 1      TERRITORY 10    Sales - Product 4               Domestic Locations         6257.97                6257.97                 -             Region 1               LOB 1     Sales - Product 4               -6257.97

2020       wk1        January 2020      Qtr 1      TERRITORY 10    Sales - Product 5               Domestic Locations         209565.81                209565.81            -             Region 1               LOB 1     Sales - Product 5               -209565.81

[vanessafvg]

can you post a screen shot of what you are doing to filter it?  also a screen shot of your data  and how it is structured.