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:

  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 

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

4 Replies

  • 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