Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello All,
I have one scenario which I am trying to do
I hava table having ticket data.
Ticket No | Data Create | Day| Ticket allotted per day 1 |2017-08-01 | 1 | 26 2 |2017-08-01 | 1 | 26 3 |2017-08-02 | 2 | 26 4 |2017-08-03 | 3 | 26 5 |2017-08-03 | 3 | 26
Now the solution I want to see as follows:
Day | Actual Ticket Per Day | Actual Running Total | Allotted Running Total 1 | 2 | 2 | 26 2 | 1 | 3 | 52 3 | 2 | 5 | 78
Now I can calculate Actual Running Total, but I am facing issues calculating Allotted Running Total.
Any help with calculating Allotted Running Total is appreciated
Solved! Go to Solution.
Let me know if this solves your problem. I find it easier to write the code based on your data:
https://www.dropbox.com/s/b2iv4gtmoofx5kb/Running%20total.pbix?dl=0
Have fun with DAX!
Alberto Ferrari
http://www.sqlbi.com
Is the value of Ticket allotted per day the same for all the rows in the same day? If so, you can do as follows:
Allotted Running Total := VAR CurrentDay = MAX ( Table[Day] ) CALCULATE ( SUMX ( SUMMARIZE ( Table, Table[Day], Table[Ticket allotted per day] ), Table[Ticket allotted per day] ), Table[Day] <= CurrentDay )
If, on the other hand, the value might be different for the same day, then this code will result in a wrong result (but, at this point, you have a modeling issue).
That said, it would be better to store the "ticket alloted per day" in a table containing one row per day, in that case the solution would be a much better one.
I am not getting the desired result.
What am I getting is same value for throught out the days. i.e. 26
Make sure to drag the date-field from your calendar-table into your report.
If your measures reference 2 different tables, you need a table with a date that is connected to both of them. Only date-fields from this table will lead to correct results in your report!
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Well, prepare a PBIX file with some demo data, and I will send you the right code. Writing blind is always challenging, I probably missed something.
Have fun with DAX!
Alberto Ferrari
http://www.sqlbi.com
Hello Alberto,
Let me put my query this way. And i would appreciate if you can help me.
Lets not consider me having an anyother table for Ticket Allotment. I am just having one ticket table.
Now Ticket allotment is calculated in following way.
Total Number of Ticket Allocated/Number of days in a month.
For Example: September, 800/30 = 26.67 ~ 27
So without having Ticket Allotment table is it possible to calculate Running Ticket Allotment.
Thanks in Advance
Let me know if this solves your problem. I find it easier to write the code based on your data:
https://www.dropbox.com/s/b2iv4gtmoofx5kb/Running%20total.pbix?dl=0
Have fun with DAX!
Alberto Ferrari
http://www.sqlbi.com
I have a table with ticket allocated per day month wise.
This number comes from a monthly allocated ticket / number of days in month. So I created a monthwise table having ticket allocated per day
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
77 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |