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

[LucasReddy09]

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 1Screensht 2Screenshot 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

[LucasReddy09]

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 . 

[gmsamborn]

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.)

[LucasReddy09]

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

Screenshot 2: 

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 !!

[Anonymous]

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