Hi everyone at PowerBi Community,
I have a question. I am very new to DAX, so please bear with me. I read all over the forums, but couldn't get to anything that can answer my question.
Our Company, has a ticket system with a barcode on it. That goes through scanners and it reads the barcodes. After each success hit the ticket get removed and doesn't get scanned again. For example if we have ticket 1 and 2.
TicketId ScanOutCome ScanNum ScanDate
1 Success 1 2018-09-10
2 Fail 1 2018-09-10
TicketId ScanOutCome ScanNum ScanDate
2 Success 2 2018-09-11
This is how it works 
hope thats simple enough...
I need to write some dax that will calculate the number of scan percentages per TicketID grouped together but dynamically.
Let me go more in depth with the data
here is a sample of our daily data
| TicketID | TicketType | Run | RunOutcome | RunFlag | DateRunNumber | RunDate |
| 37113811 | 5 | 1 | Fail | 1 | 1 | 2018-01-01 |
| 37177825 | 6 | 1 | Fail | 1 | 1 | 2018-01-01 |
| 36933501 | 9 | 1 | Fail | 1 | 1 | 2018-01-01 |
| 36901062 | 10 | 1 | Fail | 1 | 1 | 2018-01-01 |
| 37385091 | 5 | 1 | Fail | 1 | 1 | 2018-01-01 |
| 36849739 | 6 | 1 | Success | 1 | 1 | 2018-01-01 |
| 37023405 | 9 | 1 | Success | 1 | 1 | 2018-01-01 |
| TicketID | TicketType | Run | RunOutcome | RunDate | ||
| 37113811 | 5 | 2 | Fail | 1 | 1 | 2018-01-02 |
| 37177825 | 6 | 2 | Fail | 1 | 1 | 2018-01-02 |
| 36933501 | 9 | 2 | Fail | 1 | 1 | 2018-01-02 |
| 36901062 | 10 | 2 | Fail | 1 | 1 | 2018-01-02 |
| 37385091 | 5 | 2 | Success | 1 | 1 | 2018-01-02 |
| 36849739 | 6 | 2 | Not Ran | 0 | Null | Null |
| 37023405 | 9 | 2 | Not Ran | 0 | Null | Null |
| TicketID | TicketType | Run | RunOutcome | RunFlag | DateRunNumber | RunDate |
| 37113811 | 5 | 3 | Fail | 1 | 1 | 2018-01-03 |
| 37177825 | 6 | 3 | Fail | 1 | 1 | 2018-01-03 |
| 36933501 | 9 | 3 | Success | 1 | 1 | 2018-01-03 |
| 36901062 | 10 | 3 | Success | 1 | 1 | 2018-01-03 |
| 37385091 | 5 | 3 | Not Ran | 0 | Null | Null |
| 36849739 | 6 | 3 | Not Ran | 0 | Null | Null |
| 37023405 | 9 | 3 | Not Ran | 0 | Null | Null |
| TicketID | TicketType | Run | RunOutcome | RunFlag | DateRunNumber | RunDate |
| 37113811 | 5 | 4 | Fail | 1 | 1 | 2018-01-04 |
| 37177825 | 6 | 4 | Success | 1 | 1 | 2018-01-04 |
| 36933501 | 9 | 4 | Not Ran | 0 | Null | Null |
| 36901062 | 10 | 4 | Not Ran | 0 | Null | Null |
| 37385091 | 5 | 4 | Not Ran | 0 | Null | Null |
| 36849739 | 6 | 4 | Not Ran | 0 | Null | Null |
| 37023405 | 9 | 4 | Not Ran | 0 | Null | Null |
| TicketID | TicketType | Run | RunOutcome | RunFlag | DateRunNumber | RunDate |
| 37113811 | 5 | 5 | Success | 1 | 2 | 2018-01-04 |
| 37177825 | 6 | 5 | Not Ran | 0 | Null | Null |
| 36933501 | 9 | 5 | Not Ran | 0 | Null | Null |
| 36901062 | 10 | 5 | Not Ran | 0 | Null | Null |
| 37385091 | 5 | 5 | Not Ran | 0 | Null | Null |
| 36849739 | 6 | 5 | Not Ran | 0 | Null | Null |
| 37023405 | 9 | 5 | Not Ran | 0 | Null | Null |
As we can see we have a ticket ID, we have ticket types we have our runoutcomes/runflags on a certain day. This is what I want to get to at the end of a certain day.
The percentages of the run counts of all ticket ID's as at a certain day
| Percent On 2018-01-05 | |||||
| Count 5 | Count 4 | Count 3 | Count 2 | Count 1 | |
| 26.31% | 21.05% | 31.56% | 10.52% | 10.52% | |
| TicketID | TicketType | RunCount | LastRunDate | RunCountPercentage | |
| 37113811 | 5 | 5 | 2018-01-04 | 26.31% | |
| 37177825 | 6 | 4 | 2018-01-04 | 21.05% | |
| 36933501 | 9 | 3 | 2018-01-03 | 15.78% | |
| 36901062 | 10 | 3 | 2018-01-03 | 15.78% | 100% |
| 37385091 | 5 | 2 | 2018-01-02 | 10.52% | |
| 36849739 | 6 | 1 | 2018-01-01 | 5.26% | |
| 37023405 | 9 | 1 | 2018-01-01 | 5.26% | |
| Percent On 2018-01-03 | |||||
| Count 5 | Count 4 | Count 3 | Count 2 | Count 1 | |
| 0.00% | 0.00% | 60.00% | 20.00% | 20.00% | |
| TicketID | TicketType | RunCount | LastRunDate | RunCountPercentage | |
| 36933501 | 9 | 3 | 2018-01-03 | 30.00% | |
| 36901062 | 10 | 3 | 2018-01-03 | 30.00% | |
| 37385091 | 5 | 2 | 2018-01-02 | 20.00% | 100% |
| 36849739 | 6 | 1 | 2018-01-01 | 10.00% | |
| 37023405 | 9 | 1 | 2018-01-01 | 10.00% | |
Guys i know this is a mouth full 
but all i want is.
| Percent On 2018-01-05 | ||||
| Count 5 | Count 4 | Count 3 | Count 2 | Count 1 |
| 26.31% | 21.05% | 31.56% | 10.52% | 10.52% |
| Percent On 2018-01-03 | ||||
| Count 5 | Count 4 | Count 3 | Count 2 | Count 1 |
| 0.00% | 0.00% | 60.00% | 20.00% | 20.00% |
@Anonymous,
Create a date table using DAX below.
Date = CALENDARAUTO()
Then append your tables into a new table named Append1. Create the following measures in the append table.
RunCount = CALCULATE(COUNTROWS(FILTER(Append1,Append1[RunOutcome]<>"Not Ran")),FILTER(Append1,Append1[RunDate]<=SELECTEDVALUE('Date'[Date])))
AllRuncount = SUMX(ALL(Append1),[RunCount])
LastRundate = var selectedvalue=SELECTEDVALUE('Date'[Date]) return CALCULATE(MAX(Append1[RunDate]),ALLEXCEPT(Append1,Append1[TicketID]),filter(Append1,Append1[RunDate]<=selectedvalue))
RunCountPercentage = [RunCount]/[AllRuncount]
Create a slicer using Date field in your date table, and click date value to show relevant values. You can check more details in attached PBIX file.
Regards,
Lydia
@Anonymous,
Create a date table using DAX below.
Date = CALENDARAUTO()
Then append your tables into a new table named Append1. Create the following measures in the append table.
RunCount = CALCULATE(COUNTROWS(FILTER(Append1,Append1[RunOutcome]<>"Not Ran")),FILTER(Append1,Append1[RunDate]<=SELECTEDVALUE('Date'[Date])))
AllRuncount = SUMX(ALL(Append1),[RunCount])
LastRundate = var selectedvalue=SELECTEDVALUE('Date'[Date]) return CALCULATE(MAX(Append1[RunDate]),ALLEXCEPT(Append1,Append1[TicketID]),filter(Append1,Append1[RunDate]<=selectedvalue))
RunCountPercentage = [RunCount]/[AllRuncount]
Create a slicer using Date field in your date table, and click date value to show relevant values. You can check more details in attached PBIX file.
Regards,
Lydia
Helpful resources
Microsoft Fabric Learn Together
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Power BI Monthly Update - April 2024
Check out the April 2024 Power BI update to learn about new features.
Fabric Community Update - April 2024
Find out what's new and trending in the Fabric Community.
