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
CrownWorker
Helper I
Helper I

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

@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.

View solution in original post

8 REPLIES 8
TomMartens
Super User
Super User

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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.

 

 

@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.

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.


 

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.

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".

@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.

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

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.