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
LuisMP
Frequent Visitor

See how much of a category makes the max value for a given date

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.

LuisMP_2-1666122851838.png

 

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!

2 ACCEPTED SOLUTIONS
v-tangjie-msft
Community Support
Community Support

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.

vtangjiemsft_0-1666173542990.png

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. 

View solution in original post

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

6 REPLIES 6
v-tangjie-msft
Community Support
Community Support

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.

vtangjiemsft_0-1666173542990.png

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. 


DateGlobalCategorySum of Value
9/21/2022Global CategoriesCategory 192843
9/21/2022Global CategoriesCategory 223374
9/21/2022Global CategoriesCategory 379860
9/21/2022Global CategoriesCategory 43971
9/21/2022Global CategoriesCategory 5106428
9/21/2022Global CategoriesCategory 678199
9/21/2022Global CategoriesCategory 714823
9/21/2022Global CategoriesCategory 920612
9/21/2022Global CategoriesUNDEFINED9766
9/22/2022Global CategoriesCategory 158623
9/22/2022Global CategoriesCategory 219484
9/22/2022Global CategoriesCategory 393853
9/22/2022Global CategoriesCategory 46440
9/22/2022Global CategoriesCategory 5152484
9/22/2022Global CategoriesCategory 691298
9/22/2022Global CategoriesCategory 79509
9/22/2022Global CategoriesCategory 92540
9/22/2022Global CategoriesUNDEFINED2812



Hope this helps haha 

Hi,

Based on the data that you have shared, show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

The expected result should be a matrix that shows that maximum of the month which should be 437043

LuisMP_0-1666623696217.png

 

Right now what is showing is the sum of both days when I drill up

LuisMP_1-1666623804371.png

 

 

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
ryan_mayu
Super User
Super User

@LuisMP 

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.