Tickets per month of a half year view

[Cypher294]

HI,

I've got the following situation which I can not handle currently:

 

For visualization a matrix was used.

The first column shows an affected service and the next ones the count of incidents within this service.

The service and the date are attributes for each incident within the datasource.

e.g. in 2017 10 Incidents with the category basis services were created.

HJ means half year which was mapped based on the month of incident occurance.

But the issue is that I do not want to show the absolute number of Incidents within the first half ( JAN-JUN ) of the year 2017 ( 10 which is pretty easy because of it just counts the unique incidents IDs )

I've want to calculate the average number of Incidents per month within the first half of the year 2017 ( 10 / 6 = 1,67 or another example for ECM services within 1. half 2017 would be 5 / 6 = 0,83 tickets per month within the first half of 2017 ) but the column needs to be on this half year view not on a monthly basis.

I've already tested some quick measures but the results were not correct.

Ok the short summary is that I've want to calculate the average number of tickets per month within a table which shows timeframes on a half year basis.

Maybe someone has an idea to solve this.

Thanks

[Cypher294]

Sample Data

OK here are some sample Data excel based. originally the data would be in a SQL database.

[Cypher294]

HI,

sorry I'Ve just tried to find out how to upload an excel file with some sample data but could not find the option how to attach to a comment. Could just add videos or photos.

[v-shex-msft]

HI @Cypher294,

Please upload your sample file to google drive or onedrive, and share link here.

Regards,

Xiaoxin Sheng

[v-shex-msft]

Hi @Cypher294,

Maybe you can try to use below formula if it works for your scenario.

Sample =
IF (
    ISFILTERED ( Table[HJ] ),
    DIVIDE (
        CALCULATE ( COUNT ( Table[incidents] ), VALUES ( Table[Service] ) ),
        6,
        -1
    )
)

If above not help, please share some sample data to test.

Regards,

Xiaoxin Sheng

[Greg_Deckler]

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