Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
LucasReddy09
Frequent Visitor

Max values are not propagating from week to Months and from Months to Quarter and Year

‎07-11-2024 01:24 PM

Hello All, 

I am just providing the description of the issue that i am facing below. If you look at the screenshot below where i have selected week in the Time Period Filter . The chart below is showing utilization % for each week . Now 2024 week 21 is part of May and starting from 2024 Week 22  till 2024 week 26 belongs to June Month. and 2024 week 27 till 2024  week 30 is a part July . I have region , product family(Ambient, cold etc) and DC placed on the row shelf of Matrix Visual .  Now when i collapse hierarchy of region , i want to show max value across all the Product Family for that week . Its working perfectly fine as you can see the screenshot 2. Since highest value for 2024 week 21 is 26 % across all the Product Families. For 2024 week 22 the highest value is 216 % and so on and its working as expected. The issue that i am facing is when i switch from Week to Month the  values are not coming correct  as you can see from the screenshot below 

Screenshot 1:

 

Screenshot 1Screenshot 1Screensht 2Screensht 2Screenshot 3Screenshot 3

 
 
 
 
 
 
 
 
 
 

 

 

 
 

 

 

 

 

For 2024 June , for Ambient it is showing 75% whereas it should take the max value across all the weeks for Ambient and show when the Month Parameter is Selected , So for Ambient the value should be 216 % , for Cold it should be 130 %, For Frozen it should show 0% and for Narcotis it shouls show 223 % . And when these Product Families are Collapsed then the highest value for June should be 223 % . and same logic should be applied at the Quarter level and Year Level. 

 

In a nutshell my max values from the week are not propagating or carrying forwarding  at Month Level , then Max of Month Values are not getting propagated to  Quarter and so on ...year level based on user selection .  Can you help me out here as i have been stuck here for couple of Days,

Your help is greatly appreciated . 

Will be waiting for your solution. 

 

The formula which i am currently using is as below   . Storage_Category is nothing but Produt Family only . 

 

Max Yr Qtr Mth Utilization =
VAR SelectedFlag = SELECTEDVALUE('Date Hierarchy Field Parameter'[Flag])
RETURN
IF(
    SelectedFlag = "Year",
    CALCULATE(
        MAXX(
            SUMMARIZE(
                'Date',
                'Date'[Year-Quarter],
                'Date'[Year-Month],
                'Date'[Year-Week],
                "MaxUtil",
                CALCULATE(
                    MAXX(
                        VALUES('Node View Query'[dc_region]),
                        CALCULATE(
                            MAXX(
                                VALUES('Node View Query'[Storage_category]),
                                CALCULATE(
                                    MAXX(
                                        VALUES('Node View Query'[dc]),
                                        IF(
                                            [Capacity_Value_New] > 0,
                                            DIVIDE('Node View Query'[Projection_Value], [Capacity_Value_New]),
                                            0
                                        )
                                    )
                                )
                            )
                        )
                    )
                )
            ),
            [MaxUtil]
        )
    ),
    IF(
        SelectedFlag = "Quarter",
        CALCULATE(
            MAXX(
                SUMMARIZE(
                    'Date',
                    'Date'[Year-Month],
                    'Date'[Year-Week],
                    "MaxUtil",
                    CALCULATE(
                        MAXX(
                            VALUES('Node View Query'[dc_region]),
                            CALCULATE(
                                MAXX(
                                    VALUES('Node View Query'[Storage_category]),
                                    CALCULATE(
                                        MAXX(
                                            VALUES('Node View Query'[dc]),
                                            IF(
                                                [Capacity_Value_New] > 0,
                                                DIVIDE('Node View Query'[Projection_Value], [Capacity_Value_New]),
                                                0
                                            )
                                        )
                                    )
                                )
                            )
                        )
                    )
                ),
                [MaxUtil]
            )
        ),
        IF(
            SelectedFlag = "Month",
            CALCULATE(
                MAXX(
                    SUMMARIZE(
                        'Date',
                        'Date'[Year-Week],
                        "MaxUtil",
                        CALCULATE(
                            MAXX(
                                VALUES('Node View Query'[dc_region]),
                                CALCULATE(
                                    MAXX(
                                        VALUES('Node View Query'[Storage_category]),
                                        CALCULATE(
                                            MAXX(
                                                VALUES('Node View Query'[dc]),
                                                IF(
                                                    [Capacity_Value_New] > 0,
                                                    DIVIDE('Node View Query'[Projection_Value], [Capacity_Value_New]),
                                                    0
                                                )
                                            )
                                        )
                                    )
                                )
                            )
                        )
                    ),
                    [MaxUtil]
                )
            ),
            IF(
                SelectedFlag = "Week",
                CALCULATE(
                    MAXX(
                        SUMMARIZE(
                            'Date',
                            'Date'[Year-Week],
                            "MaxUtil",
                            CALCULATE(
                                MAXX(
                                    VALUES('Node View Query'[dc_region]),
                                    CALCULATE(
                                        MAXX(
                                            VALUES('Node View Query'[Storage_category]),
                                            CALCULATE(
                                                MAXX(
                                                    VALUES('Node View Query'[dc]),
                                                    IF(
                                                        [Capacity_Value_New] > 0,
                                                        DIVIDE('Node View Query'[Projection_Value], [Capacity_Value_New]),
                                                        0
                                                    )
                                                )
                                            )
                                        )
                                    )
                                )
                            )
                        ),
                        [MaxUtil]
                    )
                )
            )
        )
    )
)

 

 

@OwenAuger  Can you please take a look at it . That would be very very helpful.

 

 

Regards,

Lucas Reddy

 

 

 

Labels:
Message 1 of 5
365 Views
0
4 REPLIES 4
LucasReddy09
Frequent Visitor

‎07-13-2024 01:50 AM

Seems like i don't have the option to upload the pbix file .Please let me know if anyone can provide me the url and i can upload both excel output and PBIX file . 

Message 4 of 5
277 Views
0
gmsamborn
Super User
Super User
In response to LucasReddy09

‎07-13-2024 09:30 AM

Hi @LucasReddy09 

 

You can upload your pbix file to OneDrive, Google Drive, We Transfer, Dropbox, etc and copy the link to your post.  (Depending on the platform, you may need to give permission.)

 

 

Message 5 of 5
247 Views
0
LucasReddy09
Frequent Visitor

‎07-13-2024 01:43 AM

Hi Scott, 

 

I am attaching the pbix file . So here is the Output i am looking along with the screenshots mentioned below 

 Below is the current view( See in Screenshot 1) with Week Selected in the Time Period Parameter . Now when i collapse EMEA , it is showing me the highest value for each week across entire Sub_Categories which is working perfectly fine . See Screenshot 2 for the same 

Screenshot 1

LucasReddy09_0-1720856967105.png

 

Screenshot 2: 

LucasReddy09_1-1720857204045.png

Attaching the PBIX as well excel which contains different scenarios based on user selected different time grain from the Time Period Slicer . 

 I need urgent help on this as  i have been struggling it with so many days . Any help or solution is greatly appreciated . 

 

Thanks to all in advance !!

 

 

Message 3 of 5
280 Views
0
v-tianyich-msft
Community Support
Community Support

‎07-11-2024 10:32 PM

Hi @LucasReddy09 ,

 

Please provide sample data as well as expected results in a table or other available format.

 

Best regards,
Community Support Team_ Scott Chang

Message 2 of 5
307 Views
0

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

View All