Dax Formula correction help

[JothiG]

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

[v-sgandrathi]

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.

[v-sgandrathi]

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]

your thankfull 

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

​Best regards,

Vishwanath

[AshokKunwar]

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

[danextian]

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. 

[LED_General]

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

[krishnakanth240]

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]

Div_name	Category	Category sorter	Item	item_sorter	Item_type	Stock_fin_year	Stock_year	Stock_month	Stock_month_name	Month_sorter	Period	period_sorter	Value	Ageing_bucket	Ageing_bucket_sorter
HO	RM	1	Yarn At warehouse	1	At WH	2025	2025	4	Apr	4	Q1	1	8104.15	0-60 Days	1
HO	RM	1	Yarn At warehouse	1	At WH	2025	2025	4	Apr	4	Q1	1	21104.72	0-60 Days	1
HO	RM	1	Yarn At warehouse	1	At WH	2025	2025	4	Apr	4	Q1	1	28478.02	0-60 Days	1
HO	RM	1	Yarn At warehouse	1	At WH	2025	2025	4	Apr	4	Q1	1	146531.79	0-60 Days	1
HO	RM	1	Yarn At warehouse	1	At WH	2025	2025	4	Apr	4	Q1	1	364557.7	0-60 Days	1
HO	RM	1	Yarn At warehouse	1	At WH	2025	2025	4	Apr	4	Q1	1	34984.5	0-60 Days	1
HO	RM	1	Yarn At warehouse	1	At WH	2025	2025	4	Apr	4	Q1	1	9755.35	0-60 Days	1
HO	RM	1	Yarn At warehouse	1	At WH	2025	2025	4	Apr	4	Q1	1	17059.41	0-60 Days	1
HO	RM	1	Yarn At warehouse	1	At WH	2025	2025	4	Apr	4	Q1	1	17193.76	0-60 Days	1
HO	RM	1	Yarn At warehouse	1	At WH	2025	2025	4	Apr	4	Q1	1	12369.36	0-60 Days	1
HO	RM	1	Yarn At warehouse	1	At WH	2025	2025	4	Apr	4	Q1	1	1968.73	0-60 Days	1
HO	RM	1	Yarn At warehouse	1	At WH	2025	2025	4	Apr	4	Q1	1	1175.36	0-60 Days	1
HO	RM	1	Yarn At warehouse	1	At WH	2025	2025	4	Apr	4	Q1	1	2827.62	0-60 Days	1
HO	RM	1	Yarn At warehouse	1	At WH	2025	2025	4	Apr	4	Q1	1	1560.06	0-60 Days	1
HO	RM	1	Yarn At warehouse	1	At WH	2025	2025	4	Apr	4	Q1	1	14625.6	0-60 Days	1
HO	RM	1	Yarn At warehouse	1	At WH	2025	2025	4	Apr	4	Q1	1	1669.77	0-60 Days	1
HO	RM	1	Yarn At warehouse	1	At WH	2025	2025	4	Apr	4	Q1	1	683.53	0-60 Days	1

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]

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)

[JothiG]

still not getting correct result.

[krishnakanth240]

Hi @JothiG 

Okay, can you confirm the columns that are referencing from each table and the relationships between the tables.

[krishnakanth240]

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)

[JothiG]

it is not working

[JothiG]

No. it is not working