Problem with Count

[Anonymous]

Hi Everyone,

I am new to Power BI and I have a count problem. In the table below, I am trying to create an analysis of how the delivery days of the following products have changed from Jan - May. The below table shows the product and delivery days in each month. Product A for example was delivered within 90 days in Jan, Feb and Mar, and delivered within 60 days in April and May. Product B however was only delivered within 60 days during the entire period, thereofre it has only one delivery days, e.t.c.

1) How can I count the products with different delivery days (i.e Products A, C and D)?

2) Can you advise on the best visual to represent the Products and their Delivers Days by Month?

PS: The 1s under the months are only representative of the delivery days and not a count of product.

Thank you for your anticipated assistance

Product	Delivery Days	Jan	Feb	Mar	April	May
A	90	1	1	1
A	60				1	1
B	60	1	1	1	1	1
C	60	1
C	45		1		1
C	90			1		1
D	45			1	1	1
D	60	1	1
E	60	1	1	1	1	1

[FrankAT]

Hi @Anonymous 

1.  Unpivot month columns in Power Query (see figure).
2. Use a table visual and the following measure:

Count Delivery Days = SUMX('Table','Table'[Delivery Days] * 'Table'[Value])

 With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[Anonymous]

Hi FrankAT,

Thanks for the message. However, I am not trying to count the delivery days. I want to count the product that have more than one delivery days value. For instance, Product A has 2 values in delivery days (i.e. 60 and 90), while Product B has only 1 value in delivery days (i.e. 60).

@FrankAT 

[Anonymous]

Update: We can ignore the months columns as I can see that it is causing some comfusion.

@mahoneypat @FrankAT 

[mahoneypat]

Please try an expression like this

Products with >1 Dates =
COUNTROWS (
    FILTER (
        VALUES ( table[product] ),
        CALCULATE (
            DISTINCTCOUNT ( table[delivery days] )
        ) > 1
    )
)

Regards,

Pat

[Anonymous]

Hi Pat,

Thanks for your effort. The measure was able to calculate the sum of Products with more than one delivery days value, however, I could not identify the products that were counted when i used the formula. This is only a dummy data, my original data has thousands of rows, and I want to know both the number of products and the name of products that has more than one delivery days value. It seems like an easy task but surprisingly it is more complicated that it looks. 

Do you have any other ideas? I appreciate your help.

@mahoneypat @FrankAT 

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[mahoneypat]

To do that, you could make a table visual with your Product column and this measure.  Products with only 1 delivery day value would be blank and get filtered out from the visual.

Count if Multiple =
VAR deldays =
    DISTINCTCOUNT ( Table[Delivery Days] )
RETURN
    IF (
        deldays > 1,
        deldays,
        BLANK ()
    )

Regards,

Pat

[FrankAT]

Hi @Anonymous 

is this what you are looking for:

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[mahoneypat]

Is this how your data are structured (with Months as columns)?  If so, the data should be unpivoted in the query editor.  Once that is done, a measure can be written that give the count of products that have >1 values of Delivery Days.

Regards,

Pat

[Anonymous]

Hi Pat,

Thanks for the message. The data is not structured as months in column. I only included it in the table to show how the delivery days changes during the month.  If we are to ignore the months column, how can I count the products that have > 1 values of delivery days. Thank you.