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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
SHILL
Frequent Visitor

Number of updates per week

Hi All, I have the below table and I need to calculate how many times a colleague name appears each week. I could easily see this by setting up visuals however I want a formula so that on some visuals I can have it as an 'average per week' rather then a total.
For example - The below would be that Colleague 1 showed 0 times week 28 , once in week 29 and once in week 30. 
Then if I selected to have a card saying weekly average for that colleague, it would be .66.

Colleague NameLast Review DateCase Id
Colleague 204/07/2022#22
Colleague 306/07/2022#54
Colleague 206/07/2022#22
Colleague 211/07/2022#31
Colleague 111/07/2022#89
Colleague 319/07/2022#55
Colleague 220/07/2022#63
Colleague 220/07/2022#22
Colleague 120/07/2022#89
Colleague 320/07/2022#55
1 ACCEPTED SOLUTION
SpartaBI
Community Champion
Community Champion

@SHILL 

 

SpartaBI_1-1660038486439.png


Appearance Count = COUNTROWS('Table') + 0

Appearance Count Pct =
VAR _appearance = [Appearance Count]
VAR _total_weeks = CALCULATE(DISTINCTCOUNT('Table'[Week Num]), REMOVEFILTERS())
RETURN
_appearance / _total_weeks

Appearance for any colleague test =
INT(
    CALCULATE(
        NOT ISEMPTY('Table'),
        REMOVEFILTERS('Table'[Colleague Name],'Table'[Case Id])
    )
)


Week Num = WEEKNUM('Table'[Last Review Date], 2)



 


2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png


Showcase Report – Contoso By SpartaBI

Full-Logo11.png

SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

View solution in original post

9 REPLIES 9
SpartaBI
Community Champion
Community Champion

@SHILL what is a week represent in your data? All days from Sunday to Saterday? Monday to Sunday?

Hi, The week  is Monday - Sunday.

SpartaBI
Community Champion
Community Champion

@SHILL 
Here is a link to download a sample solution file:
Number of updates per week 2022-08-05.pbix

2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png


Showcase Report – Contoso By SpartaBI

Full-Logo11.png

SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Thanks Sparta - Unfortunately due to network restrictions within my workplace I am unable to download this - Is there a measure you can put in the comments?

SpartaBI
Community Champion
Community Champion

@SHILL sure. This is the measure:

 

Appearance Count = 
VAR _appearance = COUNTROWS('Table')
VAR _total_weeks = CALCULATE(DISTINCTCOUNT('Table'[Week Num]), REMOVEFILTERS())
RETURN
_appearance / _total_weeks

 


And this is how it looks in the file:

SpartaBI_0-1659703251200.png

 


2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png


Showcase Report – Contoso By SpartaBI

Full-Logo11.png

SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Thank you - Its not quiet doing what I want however. I want it to show that 
Colleague 1 'appeared' in week 28 0 times, Week 29 once, Week 30 once. 

SpartaBI
Community Champion
Community Champion

Hi Sparta, Sorry I cannot download due to restrictions - Could you put in comments again please?

SpartaBI
Community Champion
Community Champion

@SHILL 

 

SpartaBI_1-1660038486439.png


Appearance Count = COUNTROWS('Table') + 0

Appearance Count Pct =
VAR _appearance = [Appearance Count]
VAR _total_weeks = CALCULATE(DISTINCTCOUNT('Table'[Week Num]), REMOVEFILTERS())
RETURN
_appearance / _total_weeks

Appearance for any colleague test =
INT(
    CALCULATE(
        NOT ISEMPTY('Table'),
        REMOVEFILTERS('Table'[Colleague Name],'Table'[Case Id])
    )
)


Week Num = WEEKNUM('Table'[Last Review Date], 2)



 


2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png


Showcase Report – Contoso By SpartaBI

Full-Logo11.png

SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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