Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Count of Monthly Averages Over a Limit

Hello,

 

I have a table with the following columns:

 

Month / Year / Member ID /  Metric A / Monthly Average of Metric A

 

From this, is there a way to create a new table that includes the above columns plus a new column with a count of the number of times the monthly average of Metric A for each Member ID has been over 550 for the prior 5 months before? In other words the coulums in the new table would be Month,  Year,  Member ID,  Monthly Average of Metric A and Count of instances where the monthly average of Metric A for each Member ID is over 550 for that month and 5 months prior.

 

Thank you.

 

2 ACCEPTED SOLUTIONS

Hi @Anonymous 

I found a workaround

Create a column in your table

year-month = FORMAT('Table'[Date],"yyyy-mm")

Then create a new table

Table 2 =
FILTER (
    ADDCOLUMNS (
        SUMMARIZE (
            'Table',
            'Table'[Member],
            'Table'[year-month],
            "avg", AVERAGE ( 'Table'[Metric A] )
        ),
        "rank", RANKX ( 'Table', [year-month],, ASC, DENSE ),
        "flag", IF (
            DATEDIFF ( MAX ( 'calendar'[Date] ), TODAY (), MONTH ) < 5
                && [AVG] > 550,
            1,
            0
        )
    ),
    DATEDIFF ( MAX ( 'calendar'[Date] ), TODAY (), MONTH ) < 5
)

Then create a measure in this new table

Measure =
CALCULATE (
    SUM ( 'Table 2'[flag] ),
    FILTER (
        ALLEXCEPT ( 'Table 2', 'Table 2'[Member] ),
        'Table 2'[rank] <= MAX ( 'Table 2'[rank] )
            && [flag] = 1
    )
)

Capture12.JPG

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Hi @Anonymous 

I use DAX to create the column and measures.

If you want to create such column in Power Query, 

create a column using "date.to.text"

Date.ToText([Date], "yyyy-MM")

 

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
Greg_Deckler
Super User
Super User

The answer is almost certainly yes. You could create a new table that uses ADDCOLUMNS that takes your original table and adds the desired column. However, sample data would be required to create and test this. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490...

 

That being said, see if my Time Intelligence the Hard Way provides a means to an end.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Hello,

 

I should clarify. By " ...create a new table", I meant a Visual in Table format. I have a Visual with Month / Year / Member ID / Average of Metric A. The hangup is with creating the column that has for each Member ID, a total Count of the Monthly Averages  of Metric A that are over 550 for the given month plus the 5 months previous. The link provided for https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490... did not work. But I think I can provide sample data if needed.

 

Thanks very much.

Hi @Anonymous 

I'd like to help you.

You could provide a simple example and expected result for your example.

Then it may be easier to provide a valid solution for you.

 

Best Regards
Maggie

 

Anonymous
Not applicable

Hi,   Here is sample data and desired result. Thanks very much.

 

 

Raw Data

    
      
 

Date

Member

Metric A

  
 

6/1/2019

A

100

  
 

6/3/2019

A

400

  
 

6/4/2019

C

600

  
 

6/8/2019

C

800

  
 

7/1/2019

B

300

  
 

7/3/2019

B

100

  
 

7/5/2019

C

400

  
 

8/2/2019

C

700

  
 

8/3/2019

C

500

  
 

8/3/2019

A

400

  
 

8/3/2019

A

800

  
 

8/4/2019

B

400

  
 

8/5/2019

B

200

  
      
 

Desired Output

   
      
 

Month

Year

Member

Monthly Av Metric A

Running Count of Monthly Av. Metric A >550

 

June

2019

A

250

0

 

June

2019

C

700

1

 

July

2019

B

200

0

 

July

2019

C

400

0

 

August

2019

C

600

2

 

August

2019

A

600

1

 

August

2019

B

300

0

      

Hi @Anonymous 

I found a workaround

Create a column in your table

year-month = FORMAT('Table'[Date],"yyyy-mm")

Then create a new table

Table 2 =
FILTER (
    ADDCOLUMNS (
        SUMMARIZE (
            'Table',
            'Table'[Member],
            'Table'[year-month],
            "avg", AVERAGE ( 'Table'[Metric A] )
        ),
        "rank", RANKX ( 'Table', [year-month],, ASC, DENSE ),
        "flag", IF (
            DATEDIFF ( MAX ( 'calendar'[Date] ), TODAY (), MONTH ) < 5
                && [AVG] > 550,
            1,
            0
        )
    ),
    DATEDIFF ( MAX ( 'calendar'[Date] ), TODAY (), MONTH ) < 5
)

Then create a measure in this new table

Measure =
CALCULATE (
    SUM ( 'Table 2'[flag] ),
    FILTER (
        ALLEXCEPT ( 'Table 2', 'Table 2'[Member] ),
        'Table 2'[rank] <= MAX ( 'Table 2'[rank] )
            && [flag] = 1
    )
)

Capture12.JPG

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

Great.

My only question  is regarding creation of the new column using "...year-month = FORMAT('Table'[Date],"yyyy-mm")...". With that formula I get the following error:  "Expression.Error: The name 'FORMAT' wasn't recognized.  Make sure it's spelled correctly." I guess I'm in the Power Query environment and not DAX. Is there a workaround?

 

Many thanks.

Hi @Anonymous 

I use DAX to create the column and measures.

If you want to create such column in Power Query, 

create a column using "date.to.text"

Date.ToText([Date], "yyyy-MM")

 

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.