Forum Discussion

JothiG's avatar
JothiG
Helper III
7 months ago
Solved

Dax Formula correction help

This is my DAX 

8_percen_of_val_cur_yr =
VAR SelYear     = SELECTEDVALUE(Inventory_stock_dtl[Stock_fin_year])
VAR SelPeriod  = SELECTEDVALUE(Inventory_stock_dtl[period_sorter])
VAR SelMonth    = SELECTEDVALUE(Inventory_stock_dtl[Month_sorter])

-- If No Year selected, take latest year in data
VAR DefaultYear = CALCULATE(MAX(Inventory_stock_dtl[Stock_fin_year]), ALL(Inventory_stock_dtl))
VAR FinalYear = IF(ISBLANK(SelYear), DefaultYear, SelYear)

var DefaultPeriod =  CALCULATE(MAX(Inventory_stock_dtl[period_sorter]), FILTER(Inventory_stock_dtl,
            Inventory_stock_dtl[Stock_fin_year] = FinalYear))
var FinalPeriod  = IF(ISBLANK(SelPeriod), DefaultPeriod, SelPeriod)

var DefaultMonth = CALCULATE(MAX(Inventory_stock_dtl[Month_sorter]), FILTER(Inventory_stock_dtl,
            Inventory_stock_dtl[Stock_fin_year] = FinalYear))
VAR QuarterLastMonth =
     SWITCH( TRUE(),
        SelPeriod = 1, 6,
        SelPeriod = 2, 9,
        SelPeriod = 3, 12,
        SelPeriod = 4, 15,
        BLANK()
    )
-- Month priority logic
VAR FinalMonth =
    IF(NOT ISBLANK(SelMonth), SelMonth,
    IF(NOT ISBLANK(SelPeriod), QuarterLastMonth,
    DefaultMonth))   -- Default when nothing selected → March




  var val_bucket = CALCULATE(
        SUM(Inventory_stock_dtl[Value]),
        Inventory_stock_dtl[Stock_fin_year] = FinalYear,
        Inventory_stock_dtl[Month_sorter] = FinalMonth
    )
  var tot_val =  CALCULATE(
        SUM(Inventory_stock_dtl[Value]),
        FILTER(
            ALL(Inventory_stock_dtl),
            Inventory_stock_dtl[Stock_fin_year] = FinalYear &&
             Inventory_stock_dtl[Month_sorter] = FinalMonth
        )
    )
return
DIVIDE(val_bucket,tot_val,0)

i want to calculate the percentage with respect to ageing_bucket(marked in pencil) .the formula is 

(ageing bucket value) / (Total of all ageing bucket value) . so i will get total percentage is 100. The problem is in my dax is not including filter of div_name , item category, item while calculating total (Total of all ageing bucket value) .how to do this

Need Help
  • AshokKunwar's avatar
    AshokKunwar
    7 months ago

    Hello! The issue lies in your tot_val variable. By using ALL(Inventory_stock_dtl), you are telling Power BI to ignore every single filter on that table, which is why your Division, Category, and Item filters are being ignored.

    ​To calculate the total across all aging buckets while keeping your other slicers active, you should replace ALL with ALLEXCEPT or specifically remove the filter only from the Ageing Bucket column.

    ​Try updating your tot_val variable to this:

    var tot_val = 

    CALCULATE(

        SUM(Inventory_stock_dtl[Value]),

        ALLEXCEPT(

            Inventory_stock_dtl, 

            Inventory_stock_dtl[Stock_fin_year], 

            Inventory_stock_dtl[Month_sorter],

            Inventory_stock_dtl[div_name], 

            Inventory_stock_dtl[item_category], 

            Inventory_stock_dtl[item

    ]

        )

    )

     

    Alternatively, a cleaner way using REMOVEFILTERS:

    var tot_val = 

    CALCULATE(

        SUM(Inventory_stock_dtl[Value]),

        REMOVEFILTERS(Inventory_stock_dtl[Ageing_Bucket_Column_Name]), -- Replace with your actual bucket column name

        Inventory_stock_dtl[Stock_fin_year] = FinalYear,

        Inventory_stock_dtl[Month_sorter] = FinalMo

    nth

    )

     

    Why this works:

    ALLEXCEPT: It clears all filters except for the ones you list. By listing Year, Month, Division, and Category, those filters stay active while the "Ageing Bucket" filter is cleared to get the grand total.

    REMOVEFILTERS: This is the modern way. It only removes the filter from the specific "Ageing Bucket" column, allowing all other slicers (Division, Item, etc.) to continue affecting the total.

    I hope this helps you get that 100% total! If this resolves your issue, please mark this post as an "Accepted Solution." Happy New Year!

    Best regards,

    Vishwanath

     

     

14 Replies

  • krishnakanth240's avatar
    krishnakanth240
    Super User
    7 months ago

    Hi JothiG 

     

    Can you please check this measure and let us know the result.

     

    8_percen_of_val_cur_yr =
    VAR SelYear = SELECTEDVALUE(Inventory_stock_dtl[Stock_fin_year])
    VAR SelPeriod = SELECTEDVALUE(Inventory_stock_dtl[period_sorter])
    VAR SelMonth = SELECTEDVALUE(Inventory_stock_dtl[Month_sorter])

    VAR DefaultYear =
    CALCULATE(
    MAX(Inventory_stock_dtl[Stock_fin_year]),
    ALL(Inventory_stock_dtl)
    )

    VAR FinalYear =
    IF(ISBLANK(SelYear), DefaultYear, SelYear)

    VAR DefaultPeriod =
    CALCULATE(
    MAX(Inventory_stock_dtl[period_sorter]),
    Inventory_stock_dtl[Stock_fin_year] = FinalYear
    )

    VAR FinalPeriod =
    IF(ISBLANK(SelPeriod), DefaultPeriod, SelPeriod)

    VAR DefaultMonth =
    CALCULATE(
    MAX(Inventory_stock_dtl[Month_sorter]),
    Inventory_stock_dtl[Stock_fin_year] = FinalYear
    )

    VAR QuarterLastMonth =
    SWITCH(
    TRUE(),
    FinalPeriod = 1, 6,
    FinalPeriod = 2, 9,
    FinalPeriod = 3, 12,
    FinalPeriod = 4, 15
    )

    VAR FinalMonth =
    IF(
    NOT ISBLANK(SelMonth), SelMonth,
    IF(NOT ISBLANK(SelPeriod), QuarterLastMonth, DefaultMonth)
    )

    -- Value for current ageing bucket
    VAR val_bucket =
    CALCULATE(
    SUM(Inventory_stock_dtl[Value]),
    Inventory_stock_dtl[Stock_fin_year] = FinalYear,
    Inventory_stock_dtl[Month_sorter] = FinalMonth
    )

    -- Total of ALL ageing buckets (but keep div, item, category filters)
    VAR tot_val =
    CALCULATE(
    SUM(Inventory_stock_dtl[Value]),
    Inventory_stock_dtl[Stock_fin_year] = FinalYear,
    Inventory_stock_dtl[Month_sorter] = FinalMonth,
    REMOVEFILTERS(Inventory_stock_dtl[ageing_bucket])
    )

    RETURN
    DIVIDE(val_bucket, tot_val, 0)

     

  • krishnakanth240's avatar
    krishnakanth240
    Super User
    7 months ago

    Hi JothiG 

     

    Okay, could you please let us know the exact logic and the output you want to view from the visual.

     

    If you can provide the sample data, that would be helpful.

     

     

    • JothiG's avatar
      JothiG
      Helper III
      7 months ago

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

      Div_nameCategoryCategory sorterItemitem_sorterItem_typeStock_fin_yearStock_yearStock_monthStock_month_nameMonth_sorterPeriodperiod_sorterValueAgeing_bucketAgeing_bucket_sorter
      HORM1Yarn At warehouse1At WH202520254Apr4Q118104.150-60 Days1
      HORM1Yarn At warehouse1At WH202520254Apr4Q1121104.720-60 Days1
      HORM1Yarn At warehouse1At WH202520254Apr4Q1128478.020-60 Days1
      HORM1Yarn At warehouse1At WH202520254Apr4Q11146531.790-60 Days1
      HORM1Yarn At warehouse1At WH202520254Apr4Q11364557.70-60 Days1
      HORM1Yarn At warehouse1At WH202520254Apr4Q1134984.50-60 Days1
      HORM1Yarn At warehouse1At WH202520254Apr4Q119755.350-60 Days1
      HORM1Yarn At warehouse1At WH202520254Apr4Q1117059.410-60 Days1
      HORM1Yarn At warehouse1At WH202520254Apr4Q1117193.760-60 Days1
      HORM1Yarn At warehouse1At WH202520254Apr4Q1112369.360-60 Days1
      HORM1Yarn At warehouse1At WH202520254Apr4Q111968.730-60 Days1
      HORM1Yarn At warehouse1At WH202520254Apr4Q111175.360-60 Days1
      HORM1Yarn At warehouse1At WH202520254Apr4Q112827.620-60 Days1
      HORM1Yarn At warehouse1At WH202520254Apr4Q111560.060-60 Days1
      HORM1Yarn At warehouse1At WH202520254Apr4Q1114625.60-60 Days1
      HORM1Yarn At warehouse1At WH202520254Apr4Q111669.770-60 Days1
      HORM1Yarn At warehouse1At WH202520254Apr4Q11683.530-60 Days1

       

       

      Logic :

      Visual : Area Chart
      X -Axis : Fin Year 
      Y-Axis : Percentage of Ageing_Bucket

      Legend : Ageing Bucket

      Y -Axis shows Ageing bucket (Eg: 0-30 Days,31-60 Days etc) and the ageing bucket percentage shows inside area chart: now i want calculate percentage  = (single ageing bucket value/ All Ageing bucket value) *100 . sum of all ageing bucket percentage should be 100.

       

       

      • krishnakanth240's avatar
        krishnakanth240
        Super User
        7 months ago

        Hi JothiG 

         

        1)Total Stock Value =
        SUM ( 'Stock'[Value] )

         

        2)Ageing Bucket % =
        DIVIDE(
        [Total Stock Value],
        CALCULATE(
        [Total Stock Value],
        ALL ( 'Stock'[Ageing_bucket] )
        ),
        0
        )

         

        Numerator → Stock value for current ageing bucket
        Denominator → Total stock value across all ageing buckets
        ALL(Ageing_bucket) removes only bucket filter
        Result = percentage contribution
        Sum of all buckets = 100%

         

        Area Chart
        X-Axis → Stock_fin_year
        Y-Axis → Ageing Bucket %
        Legend → Ageing_bucket
        Data labels → ON (Percentage)

  • LED_General's avatar
    LED_General
    Advocate I
    7 months ago

    I am quite sure this measure is unnecessary complicated. What do you exactly want to achieve?

  • danextian's avatar
    danextian
    Super User
    7 months ago

    Hi JothiG 

    Where are these columns coming from?

    div_name , item category, item

    If they are from Inventory_stock_dtl then it is expected they are overrriden as you applied ALL  to this table. 

  • AshokKunwar's avatar
    AshokKunwar
    Continued Contributor
    7 months ago

    Hello! The issue lies in your tot_val variable. By using ALL(Inventory_stock_dtl), you are telling Power BI to ignore every single filter on that table, which is why your Division, Category, and Item filters are being ignored.

    ​To calculate the total across all aging buckets while keeping your other slicers active, you should replace ALL with ALLEXCEPT or specifically remove the filter only from the Ageing Bucket column.

    ​Try updating your tot_val variable to this:

    var tot_val = 

    CALCULATE(

        SUM(Inventory_stock_dtl[Value]),

        ALLEXCEPT(

            Inventory_stock_dtl, 

            Inventory_stock_dtl[Stock_fin_year], 

            Inventory_stock_dtl[Month_sorter],

            Inventory_stock_dtl[div_name], 

            Inventory_stock_dtl[item_category], 

            Inventory_stock_dtl[item

    ]

        )

    )

     

    Alternatively, a cleaner way using REMOVEFILTERS:

    var tot_val = 

    CALCULATE(

        SUM(Inventory_stock_dtl[Value]),

        REMOVEFILTERS(Inventory_stock_dtl[Ageing_Bucket_Column_Name]), -- Replace with your actual bucket column name

        Inventory_stock_dtl[Stock_fin_year] = FinalYear,

        Inventory_stock_dtl[Month_sorter] = FinalMo

    nth

    )

     

    Why this works:

    ALLEXCEPT: It clears all filters except for the ones you list. By listing Year, Month, Division, and Category, those filters stay active while the "Ageing Bucket" filter is cleared to get the grand total.

    REMOVEFILTERS: This is the modern way. It only removes the filter from the specific "Ageing Bucket" column, allowing all other slicers (Division, Item, etc.) to continue affecting the total.

    I hope this helps you get that 100% total! If this resolves your issue, please mark this post as an "Accepted Solution." Happy New Year!

    Best regards,

    Vishwanath

     

     

  • v-sgandrathi's avatar
    v-sgandrathi
    Community Support
    7 months ago

    Hi JothiG,

    Thank you AshokKunwar krishnakanth240 danextian LED_General for your responses to the query.

    we haven't heard back from you regarding our last response and wanted to check if your issue has been resolved.

    Should you have any further questions, feel free to reach out.
    Thank you for being a part of the Microsoft Fabric Community Forum!

    • AshokKunwar's avatar
      AshokKunwar
      Continued Contributor
      7 months ago

      your thankfull 

      If this solves your issue, please mark this as an "Accepted Solution" to help others in the community.

      ​Best regards,

      Vishwanath

  • v-sgandrathi's avatar
    v-sgandrathi
    Community Support
    7 months ago

    Hi JothiG,

     

    I wanted to follow up on our previous suggestions regarding the issue. We would love to hear back from you to ensure we can assist you further.

     

    Thank you.