Help with Unit Counting on Card and Counting between Unit Values - <100, 101-500, 501>

[lennox25]

Hi, 

Im hoping someone can help. Im having issues:

1.  Using a card to show total no of R stores between 01/01/21 and 31/12/2021 with sales unit of greater than 0. This calculates the correct number when produced via a table but will not adjust to the sales unit selection of >0. There are 63 stores >0. The card is saying 171 (which is the total count including 0 units sold. I need the card to show the correct total.
2. I need to create a measure/column to show total R stores which have Units Sold between >0 -100, and another measure/calulated column for 101 - 500 and another measure/calculated column for 501+ for the time period 01/01/21 to 31/12/21. I am getting no success. 

I have uploaded a link to the file, if anyone would like to help me. Thank you

  Power Bi Sample Report 

[Anonymous · Accepted solution]

Hi @lennox25 ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want.

1. Create two measures as below to get it:

SUnits Sold = SUM('_Units'[Units sold])

Count of Stores = 
CALCULATE (
    COUNT ( 'Table_Store'[Store Licence ID] ),
    FILTER ( 'Table_Store', [SUnits Sold] > 0 )
)

2. Put the measure [Count of Stores] onto the card visual to replace the value field with aggregation function

And if you want to get the count of stores for different unit sold range, you can follow the steps below to get it. Please find the details in Page 2 of the attachment.

1. Create a range dimension table as below screenshot using Enter Data method

2. Create a slicer using the range field in range dimension table

2. Create a measure as below to get the count of store base on the slicer selection

Measure = 
IF (
    ISFILTERED ( 'Units Sold Range'[Range] ),
    CALCULATE (
        DISTINCTCOUNT ( 'Table_Store'[Store Licence ID] ),
        FILTER (
            'Table_Store',
            SWITCH (
                SELECTEDVALUE ( 'Units Sold Range'[Range] ),
                "0-100",
                    [SUnits Sold] > 0
                        && [SUnits Sold] <= 100,
                "101-500",
                    [SUnits Sold] >= 101
                        && [SUnits Sold] <= 500,
                "500+", [SUnits Sold] > 500
            )
        )
    ),
    COUNT ( 'Table_Store'[Store Licence ID] )
)

Best Regards

[lennox25]

@Anonymous Thank you so much! This works perfectly! I really appeciate the help 🙂

Could you help me get these figures as a %?

Thank you

[Anonymous]

Hi @lennox25 ,

I'm not sure which figures you want to show as a %? I updated the formula of measure as below, please check if that is what  you want. You can find the details in the attachment.

Percent = 
VAR _selcount =
    IF (
        ISFILTERED ( 'Units Sold Range'[Range] ),
        CALCULATE (
            DISTINCTCOUNT ( 'Table_Store'[Store Licence ID] ),
            FILTER (
                'Table_Store',
                SWITCH (
                    SELECTEDVALUE ( 'Units Sold Range'[Range] ),
                    "0-100",
                        [SUnits Sold] > 0
                            && [SUnits Sold] <= 100,
                    "101-500",
                        [SUnits Sold] >= 101
                            && [SUnits Sold] <= 500,
                    "500+", [SUnits Sold] > 500
                )
            )
        ),
        COUNT ( 'Table_Store'[Store Licence ID] )
    )
VAR _allcount =
    CALCULATE (
        COUNT ( 'Table_Store'[Store Licence ID] ),
        REMOVEFILTERS ( 'Units Sold Range'[Range] )
    )
RETURN
    DIVIDE ( _selcount, _allcount )

Best Regards

[lennox25]

Hi @Anonymous  Thank you and apologies for not explaining properly. In addition to the original measue (as I need them for the cards, I also need to  work out the percentage of totals for each category  so for R Stores:  0-100, 101-500, 501+ . For example if there were a total of 167 R Stores,  42  - 0-100, 98 - 101-500 and 27 500+. Then the % of 0-100 would be 25.1%, 100-500 58.7% and ‌500+ 16.2%. I need work out how to get the figures and to be able to put into pie/bar charts. Thankyou