Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a dataset that has a value for every day for every month and for every category.
I need to sum the values for each day and get the day of the month where that value is maximum. And see how much of each category makes that maximum value
So, i.e. for September 21st 2022 and September 22nd 2022 I have this values.
From that I need to sum all the values for Sep 21st and all the values for Sep 22nd and see which one is the highest for September. Then see how much of every category makes that highest value.
Hope I'm making sense here haha
Thanks in advance!
Solved! Go to Solution.
Hi @LuisMP ,
According to your description, here are my steps you can follow as a solution.
(1)We can create a calculated column.
Column = CALCULATE( SUM('Table'[Value]) ,ALLEXCEPT( 'Table', 'Table'[Date]))
(2)Then we can create a measure.
Measure = var _max_value = MAXX( ALL('Table') ,[Column])
var _t=DISTINCT(SELECTCOLUMNS( FILTER('Table','Table'[Column]=_max_value) ,"Date" , [Date]))
var _cur_date =SELECTEDVALUE('Table'[Date])
return
IF(_cur_date in _t ,SUM('Table'[Value]),BLANK())
(3)Then the result is as follows.
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi @LuisMP ,
According to your description, here are my steps you can follow as a solution.
(1)We can create a calculated column.
Column = CALCULATE( SUM('Table'[Value]) ,ALLEXCEPT( 'Table', 'Table'[Date]))
(2)Then we can create a measure.
Measure = var _max_value = MAXX( ALL('Table') ,[Column])
var _t=DISTINCT(SELECTCOLUMNS( FILTER('Table','Table'[Column]=_max_value) ,"Date" , [Date]))
var _cur_date =SELECTEDVALUE('Table'[Date])
return
IF(_cur_date in _t ,SUM('Table'[Value]),BLANK())
(3)Then the result is as follows.
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
So the base idea is to sum all the values for a given day of a given month and get the max value of that sum, that will be the maximun value for that month. Like, sum all the values from sep 1st, then all from sep 2nd and so on and on a matrix or a card see which was the maximumfor the month, say if on sep 22nd the sum of all values was the biggest, thats what I need to see and see how much the categories contribute to that sum on a clustered chart maybe or something.
Date | Global | Category | Sum of Value |
9/21/2022 | Global Categories | Category 1 | 92843 |
9/21/2022 | Global Categories | Category 2 | 23374 |
9/21/2022 | Global Categories | Category 3 | 79860 |
9/21/2022 | Global Categories | Category 4 | 3971 |
9/21/2022 | Global Categories | Category 5 | 106428 |
9/21/2022 | Global Categories | Category 6 | 78199 |
9/21/2022 | Global Categories | Category 7 | 14823 |
9/21/2022 | Global Categories | Category 9 | 20612 |
9/21/2022 | Global Categories | UNDEFINED | 9766 |
9/22/2022 | Global Categories | Category 1 | 58623 |
9/22/2022 | Global Categories | Category 2 | 19484 |
9/22/2022 | Global Categories | Category 3 | 93853 |
9/22/2022 | Global Categories | Category 4 | 6440 |
9/22/2022 | Global Categories | Category 5 | 152484 |
9/22/2022 | Global Categories | Category 6 | 91298 |
9/22/2022 | Global Categories | Category 7 | 9509 |
9/22/2022 | Global Categories | Category 9 | 2540 |
9/22/2022 | Global Categories | UNDEFINED | 2812 |
Hope this helps haha
Hi,
Based on the data that you have shared, show the expected result.
The expected result should be a matrix that shows that maximum of the month which should be 437043
Right now what is showing is the sum of both days when I drill up
Hi,
You may download my PBI file from here.
Hope this helps.
could you pls provide the sample data not the screenshot?
What's more ,pls provide the expected output based on the sample data you provided.
Proud to be a Super User!
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |