Count IF

[Anonymous]

Hello friends, how are you?

I need to count the balls different of green, someone help me to create this measure?

this measure is the average of a values in a period of time.

thanks a lot!

[v-zhangti · Accepted solution]

Hi, @Anonymous 

You can try the following methods.

Table:

Average =
SUMMARIZE (
    'Table',
    'Table'[Item],
    "Average",
        CALCULATE (
            AVERAGE ( 'Table'[%] ),
            FILTER ( ALL ( 'Table' ), [Item] = EARLIER ( 'Table'[Item] ) )
        )
)

Column:

Ball collour =
IF (
    [Average] < 0.9,
    "Yellow ball",
    IF ( [Average] > 1.1, "Red ball", "Green ball" )
)

Measure:

Count =
CALCULATE (
    COUNT ( 'Average'[Item] ),
    FILTER ( ALL ( 'Average' ), [Ball collour] <> "Green ball" )
)

Is this the output you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Whitewater100]

Hi:

What are the expected results?

Can you share some example data?

* If you wants a Daily or Monthly average it is important to have a date table, marked as a date table (on date field). I'll paste some DAX Date table code below, in case you need it.

Go to Modeling>NEW Table: (see below).

Now measures after you get your date table set and connected to your fact table on date fields.

example of measure we want average on:

Total Sales = SUM(Table[Sales Amt])

[Daily Sales Average] =averagex(    Dates',    [Total Sales])

[Daily Sales Average] =

averagex(

    'Dates',

    calculate(

        [Total Sales],

        Transaction_data[Sales_amount] <> 0

    )

)

[Monrthly Sales Average] =

averagex(

    'Dates'[Month],

    calculate(

        [Total Sales],

        Transaction_data[Sales_amount] <> 0

    )

)

Dates =

-- Specify a start date and end date

VAR StartDate = Date(2021,1,1)

VAR EndDate = Today() + 243

VAR FiscalMonthEnd = 12

-- Generate a base table of dates

VAR BaseTable = Calendar(StartDate, EndDate)

-- Add the Year for each individual date

VAR Years = ADDCOLUMNS(BaseTable,"Year",YEAR([Date]))

-- Add the calendar month and other month related data for each date

VAR Months = ADDCOLUMNS(

    Years,

    "Month",MONTH([Date]),

    "Year and Month Number",FORMAT([Date],"YYYY-MM"),

    "Year and Month Name",FORMAT([Date],"YYYY-MMM"),

    "Fiscal Year", IF( FiscalMonthEnd = 12, YEAR([Date]), IF( MONTH([DATE]) <= FiscalMonthEnd, YEAR([DATE])-1, YEAR([Date]))),

    "Fiscal Month", IF( FiscalMonthEnd = 12, MONTH([Date]),

        IF( MONTH([Date]) <= FiscalMonthEnd, FiscalMonthEnd + MONTH([Date]), MONTH([Date]) - FiscalMonthEnd))

)

-- Add the Quarter and other quarter related data for each date   

VAR Quarters = ADDCOLUMNS(

    Months,

    "Quarter",ROUNDUP(MONTH([Date])/3,0),

    "Year and Quarter",[Year] & "-Q" & ROUNDUP(MONTH([Date])/3,0))

-- Add the Day and other day related data for each date   

VAR Days = ADDCOLUMNS(

    Quarters,

    "Day",DAY([Date]),

    "Day Name",FORMAT([Date],"DDDD"),

    "Day Of Week",WEEKDAY([Date]),

    "Day Of Year", DATEDIFF (DATE(YEAR([Date]),1,1), [Date], DAY) + 1)

-- Add the Week (assuming each week starts on a Sunday) and other week related data for each date   

VAR Weeks = ADDCOLUMNS(

    Days,

    "Week Of Month (Sunday)",INT((DAY([Date])-1)/7)+1,

    "Week of Year (Sunday)",WEEKNUM([Date],1),

    "Year and Week (Sunday)",[Year] & "-W" & WEEKNUM([Date],1))

-- Add an 'Is Working Day' column which will be true for all days but Saturday and Sunday.

var WorkingDays = ADDCOLUMNS(

    Weeks,

    "Is Working Day", NOT WEEKDAY( [Date] ) IN {1,7})

RETURN WorkingDays

[Anonymous]

@Whitewater100 

Hello, I have a primary table: 

Ball Logics: if(% < 90% Yellow ball, % > 110% Red ball, % between 90% and 110% green ball)

Date	Item	%	Ball collour
26/04/2022	Item A	113,9%	Yellow ball
13/05/2022	Item A	97,7%	Green ball
16/05/2022	Item A	97,7%	Green ball
18/05/2022	Item A	85,0%	Red ball
25/04/2022	Item A	100,3%	Green ball
25/05/2022	Item A	100,3%	Green ball
27/04/2022	Item A	100,3%	Green ball
18/05/2022	Item A	81,0%	Red ball
18/05/2022	Item A	89,0%	Red ball
26/04/2022	Item B	113,9%	Yellow ball
13/05/2022	Item B	97,7%	Green ball
16/05/2022	Item B	97,7%	Green ball
18/05/2022	Item B	85,0%	Red ball
25/04/2022	Item B	100,3%	Green ball
26/04/2022	Item B	100,3%	Green ball
27/04/2022	Item B	91,0%	Green ball
18/05/2022	Item B	70,0%	Red ball
18/05/2022	Item B	50,0%	Red ball

From this table, I make a avarage table:

Average	%	Ball collour
Item A	96,1%	Green ball
Item B	89,5%	Red ball

I need to count in this Avarage table how many itens appears different of green ball, can you help me?

thanks a lot.

[Whitewater100 · Accepted solution]

Hello Again:

Since there is one yellow ball I made % both including Yellow and not including yellow. Please see file link.https://drive.google.com/file/d/1fw1_03W0Pn_eOG8CdKhOvSiwVrcXrGru/view?usp=sharing 

[v-zhangti · Accepted solution]

Hi, @Anonymous 

You can try the following methods.

Table:

Average =
SUMMARIZE (
    'Table',
    'Table'[Item],
    "Average",
        CALCULATE (
            AVERAGE ( 'Table'[%] ),
            FILTER ( ALL ( 'Table' ), [Item] = EARLIER ( 'Table'[Item] ) )
        )
)

Column:

Ball collour =
IF (
    [Average] < 0.9,
    "Yellow ball",
    IF ( [Average] > 1.1, "Red ball", "Green ball" )
)

Measure:

Count =
CALCULATE (
    COUNT ( 'Average'[Item] ),
    FILTER ( ALL ( 'Average' ), [Ball collour] <> "Green ball" )
)

Is this the output you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@Whitewater100  

@v-zhangti 

Thanks a lot !!

Have a nice week.

[Whitewater100]

Hello:

Glad it worked. Are you able to mark my reply with solution or kudos? I appreciate that. Thanks again..

[Anonymous]

@Whitewater100 

Sure, done, thanks again !!!