User Profile
Atiroocky
Frequent Visitor
Joined 3 years ago
User Widgets
Contributions
Re: Date/hour table - Create measure of day average depending on slicers
As I continue to investigate by my side, I think I found out the issue. Sometimes, the "Value" (which is calculated) is shown as "infiny" When I get rid of the "infiny" value in the filter pannel, and replace ALL by ALLSELECTED in the measure, it finally works ! day_average = AVERAGEX( ALLSELECTED(Table_date[Date_heure]), CALCULATE( [Value], VALUES (Table_date[Date] ) ))Date/hour table - Create measure of day average depending on slicers
Hello all, My request is a bit "tricky" so i tried to simplified it the most. My data ("Value") are stored in a date/hour database, with a value each hour. I created a date/time table to link all my data. I want to show a table with : first column = date/day/hour second column = maximum hour values third column = day average value (measure) day_average = AVERAGEX( ALL(Table_date[Date_heure]), CALCULATE( [Value], VALUES (Table_date[Date] ) )) The table will be "filtered" by slicers, especially "hour" slicer, which will reduce the size of a the days. To do so, I already created measures, but I’m facing an issue. The measure that calculates the "day average" values works. But it is not affected by slicers. If I want to shorten day with the slicer by 10h-19h instead of 0h-23h, the result of this measure will not change. I tried to replace the ALL() by ALLSELECTED() whitout any change of the results. I found a "workaround" (right table in the image) wich consists of showing a table with date/day lines (instead of date/time/hour). I add a mesure to calculate the max value of a day, and the average value. The results of these two measures are properly affected by slicers. So, for the date/hour table, how can I write a mesure which will calculate the day average, and take care of slicers values ? Thanks for your help.Re: Load and combine same structure .txt files from folder
Hi BA_Pete , It is not what I was expected, but your solution works. And I have to admit that it is better to do your way. I’m still in "Excel sheet" logical, manipulating data on multiple columns, one for each variable (T1,H1,T2,H2,T3,H3…), whereas thinking with "stack" data and "take" those I need in graph/chart/matrix_table with just putting the right context. Thanks for your reply. That will help me better than you think. To come back to the initial issue in PowerQuery, I managed to get the table I want (even if it is useless). As a beginner, it took me about 20 steps (14 steps after automatic powerquery combining file) to accomplish the task ! That’s time consumming. I put the steps below "in case of" : To sum up : after PowerQuery automatic combining jobs from folder (6 steps) I "group" by the field "n°" (primary key for each file. I could do it with "Date" field) Create one list by variable (Date, T, H) Extract value from list with separator "tab" for each variable Then split column by separator "tab" for each variable Then delete unwanted column I’m sure that there is a quicker way to procede. Thanks for your help.Column in datatable with dynamic function paramater
Hello, I managed to calculate a cumulative rolling day in a column of my table. I use the function "DATESINPERIOD" I would like to replace the "static" number parameter by a dynamic parameter I know that datatable are kind of "static" and can’t be updated with a "what-if" paramater. By the past I managed to generate a dynamic datable thanks to "SWITCH" function, but it was totally different needs and I can’t apply this "trick" to my actual need. Any help would be appreciated ! Thanks
