Forum Discussion

lennox25's avatar
lennox25
Post Patron
3 years ago
Solved

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

Hi,  Im hoping someone can help. Im having issues:  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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
lennox25
Post Patron
3 years ago

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's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      lennox25
      Post Patron
      3 years ago

      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