cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CrownWorker Regular Visitor
Regular Visitor

Daily Average of Categorized Trouble Types

I have a new problem.

 

My data returns all of the trouble reports for a product, they have all the key information in this dataset in order to put them into trouble types and have those dated

 

I have the following columns for the needed information.

 

Create_Time - in MM/DD/YYYY format

Trouble Description - text

TKT_ID - Text

 

I can use the Table to get a Count based on filters of date ( Month, Year, Week)

 

I have a calculated field that will categorized them based off of a table I created and imported with keywords, category and an ID field for numerical ordering.

 

The formula for that is a 2 part process

 

Part 1

 

Main_KeywordMatch =

LOOKUPVALUE(
    'Main_KW'[Main_KW],'Main_KW'[ID],
     CALCULATE(
        LASTNONBLANK('Main_KW'[ID],1),
        filter(
           VALUES(Main_KW[Main_KW]),
           SEARCH('Main_KW'[Main_KW],'Main_Table'[TRBL_DESC],1,0)<>0
        )
     ))

 

Part 2

 

MainCategory =
LOOKUPVALUE(
Main_KW[Main_Cat],Main_KW[Main_KW],Main_Table[Main_KeywordMatch]
)

 

This is how I display a count of the tickets which can be filtered by the Month,Day,Year calculated fields on the PowerBI page.

 

Chart.png

 

I want now to take the Count Value for the calculated field of Categories and divide that by the count of Days Filtered that the user selects. I am running into issues and its returning an answer of "Infinity"

 

I am wondering how to achieve this. If I havent provided enough information please let me know and I will gather what ever is missing asap.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

Re: Daily Average of Categorized Trouble Types

@CrownWorker

If you want to drag the measure to the existing Matrix visual you create, you can create the measure using DAX below, the measure value will be evaluated by each main category in your visual, and the measure value will be changed when you use slicers to filter the Matrix,.

Measure = CALCULATE(Count(Main_Table[MainCategory]))/[DateDiff]

If the above step still doesn't help, please post a screenshot about your expected result.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
8 REPLIES 8
Super User
Super User

Re: Daily Average of Categorized Trouble Types

Hey,

 

it would be really helpful if you could prepare some sample data, upload a pbix to onedrive or dropbox and share the link.

 

It's difficult (at least to me) to follow your explanations about your requirements w/o the possibility to look at some data.

 

Regards

Tom

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
CrownWorker Regular Visitor
Regular Visitor

Re: Daily Average of Categorized Trouble Types

Here is a link to a pbix that has all the customer information stripped out.

What I am trying to achieve is 2 things.

1) a static (this should be the easier of the two)

     daily average of tickets for each of my main categories

     weekly average of tickets for each of my main categories

     Yearly average of tickets for each of my main categories

2) a dynamic (user filtered)

     daily average of tickets for each of my main categories

     weekly average of tickets for each of my main categories

     Yearly average of tickets for each of my main categories

 

File location:

https://1drv.ms/u/s!AruhgvkyQfsfk3vjJCI4HdCnGSAN

 

 

let me know if this doesnt clear it up or if the data requires more information than is in the tables.

 

 

Moderator v-yuezhe-msft
Moderator

Re: Daily Average of Categorized Trouble Types

@CrownWorker,

Right click your table, and choose "New Measure" , then apply the following dax formula. And you can filter the measure using your slicers.

Measure = CALCULATE(Count(Main_Table[MainCategory]),ALLSELECTED(Main_Table))/[DateDiff]


Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
CrownWorker Regular Visitor
Regular Visitor

Re: Daily Average of Categorized Trouble Types

Hello Lydia,

 

That's not exactly the solution I am looking for but it did give me a number I can use. That will give me the total of all of my tickets during the selected period of time as a measure. I am looking to get the same thing for each individual category though as a column.

 

Thank you for the reply and work you put into this though.

Moderator v-yuezhe-msft
Moderator

Re: Daily Average of Categorized Trouble Types


 

I am looking to get the same thing for each individual category though as a column.

 

 


@CrownWorker,

Could you please explain more details about the above requirement? What output would like to display? Do you only want to create a calculated column?

Regards,

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
CrownWorker Regular Visitor
Regular Visitor

Re: Daily Average of Categorized Trouble Types

Measure = CALCULATE(Count(Main_Table[MainCategory]),ALLSELECTED(Main_Table))/[DateDiff]

 

This calculation in a format that wouls work for a column so that I can use it in a "Matrix" and when I filter by Date/Other filters all of the individual categories will update at once in the table. Right now if I try to use this it returns a value of "Infinity".

Moderator v-yuezhe-msft
Moderator

Re: Daily Average of Categorized Trouble Types

@CrownWorker

If you want to drag the measure to the existing Matrix visual you create, you can create the measure using DAX below, the measure value will be evaluated by each main category in your visual, and the measure value will be changed when you use slicers to filter the Matrix,.

Measure = CALCULATE(Count(Main_Table[MainCategory]))/[DateDiff]

If the above step still doesn't help, please post a screenshot about your expected result.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
CrownWorker Regular Visitor
Regular Visitor

Re: Daily Average of Categorized Trouble Types

That worked perfectly, thank you very much and sorry for the late reply.