Share % Measure Logic tweak help

[GanesaMoorthyGM]

Hi guys,

So i was in a new requirement of converting excel report into power bi and i somehow fixed the template and finished.
excel template:

 Now i need your help in writing one particular measure

so this is how my matrix look like so now my share contribution logic is 
See we have btq age band 0-30 days etc.. and with MC band at top like <10% etc..

so for example here mc band <10% the btq age bad 0-30 days sale is 0.09 and the total sale in btq age band is 2.16 so 0.09/2.16 = 4%
this is the logic.
so i need your help in fixing this.
this is my sale measure name

All Country MTD Sales MC Band Exclusion 
thank you.

[v-ssriganesh]

Hello @GanesaMoorthyGM, 

Could you please confirm if your query has been resolved by the provided solutions? This would be helpful for other members who may encounter similar issues.

Thank you for being part of the Microsoft Fabric Community.

[v-ssriganesh]

Hello @GanesaMoorthyGM,

We hope you're doing well. Could you please confirm whether your issue has been resolved or if you're still facing challenges? Your update will be valuable to the community and may assist others with similar concerns.

Thank you.

[mizan2390 · Accepted solution]

hi @GanesaMoorthyGM 
Can you try either of the below DAX. 

Share % = 
VAR CurrentCellSales = [All Country MTD Sales MC Band Exclusion]

VAR RowTotalSales = 
CALCULATE(
[All Country MTD Sales MC Band Exclusion],
-- Replace 'fact_sales' with your actual dimension table name if this column doesn't live in the fact table
REMOVEFILTERS( fact_sales[MakingCharge_Percent_Band] ) 
)

RETURN
DIVIDE( CurrentCellSales, RowTotalSales, 0 )

Share % = 
VAR CurrentSales = [All Country MTD Sales MC Band Exclusion]

VAR RowTotalSales = 
    CALCULATE (
        [All Country MTD Sales MC Band Exclusion],
        // This removes the filter from the Matrix columns to get the Row Total
        ALLSELECTED ( 'YourTable'[MakingCharge_Percent_Band] )
    )

RETURN
    DIVIDE ( CurrentSales, RowTotalSales, 0 ) 

 Use ALLSELECTED instead of REMOVEFILTER, If you ever apply a slicer to the page that limits the MakingCharge_Percent_Band to only a few specific bands and you want the row total to only reflect the selected bands rather than the entire database. 

if this solves your problem, please mark this as solution and give a kudos.
@me so that I don't lose this thread.

[grazitti_sapna]

Hi @GanesaMoorthyGM,

Try Below Measure

Share % =
DIVIDE(
[All Country MTD Sales MC Band Exclusion],
CALCULATE(
[All Country MTD Sales MC Band Exclusion],
REMOVEFILTERS('MC Band')
)
)

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

[GanesaMoorthyGM]

Hi. Thanks for the quick response

All Country MC Band Sales Share % =

DIVIDE(

[All Country MTD Sales MC Band Exclusion],

CALCULATE(

[All Country MTD Sales MC Band Exclusion],

REMOVEFILTERS('fact_sales'[MakingCharge_Percent_Band])

)

)i used this this results in 100% for all

[grazitti_sapna]

Hi @GanesaMoorthyGM,

Please share sample data or .pbix file

[GanesaMoorthyGM]

I should not share the data btw. I'll share my current measures and how i did this.

In Matrix Visual,
Rows:Btq_Sale_age_Band
Columns:MakingCharge_Percent_Band

Values: Qty, Sales
Measure:

All Country MTD Qty MC Band Exclusion =

VAR LastSalesDate =

    CALCULATE (

        MAX ( fact_sales[DateOnly] ),

        ALL ( fact_sales )

    )

VAR CurrentYear =

    YEAR ( LastSalesDate )

VAR CurrentMonth =

    MONTH ( LastSalesDate )

VAR TotalQty =

    CALCULATE (

        SUM ( fact_sales[foc_qty] ),

        /* ✅ Country filter */

        fact_sales[Country]

            IN { "UAE", "Qatar", "Oman", "Singapore", "US", "Kuwait" },

        /* ✅ MTD logic */

        YEAR ( fact_sales[DateOnly] ) = CurrentYear,

        MONTH ( fact_sales[DateOnly] ) = CurrentMonth,

        fact_sales[DateOnly] <= LastSalesDate,

        /* ✅ SAME EXCLUSIONS */

        fact_sales[Cluster] <> "Gold_Coins",

        NOT fact_sales[Bin_Code]

            IN { "TEP", "GEP", "TEP SALE" },

        NOT fact_sales[Pricing_type]

            IN { "Studded", "UCP", "BLANK","GOS" },

        NOT ISBLANK ( fact_sales[Pricing_type] ),

        fact_sales[Product_Group] <> "Gold spare"

    )

RETURN

COALESCE ( TotalQty, 0 )

All Country MTD Sales MC Band Exclusion =

VAR LastSalesDate =

    CALCULATE (

        MAX ( fact_sales[DateOnly] ),

        ALL ( fact_sales )

    )

VAR CurrentYear =

    YEAR ( LastSalesDate )

VAR CurrentMonth =

    MONTH ( LastSalesDate )

VAR SalesAmount =

    CALCULATE (

        SUMX (

            fact_sales,

            SWITCH (

                fact_sales[Country],

                "Qatar",      fact_sales[UCP_QAR_INR],

                "Oman",       fact_sales[UCP_OMR_INR],

                "Singapore",  fact_sales[UCP_SGD_INR],

                "US",         fact_sales[UCP_USD_INR],

                "UAE",        fact_sales[UCP_AED_INR],

                "Kuwait",     fact_sales[UCP_KDR_INR],

                0

            )

        ),

        /* ✅ Country filter */

        fact_sales[Country]

            IN { "UAE", "Qatar", "Oman", "Singapore", "US", "Kuwait" },

        /* ✅ MTD logic */

        YEAR ( fact_sales[DateOnly] ) = CurrentYear,

        MONTH ( fact_sales[DateOnly] ) = CurrentMonth,

        fact_sales[DateOnly] <= LastSalesDate,

        /* ✅ EXCLUSIONS – MATCH BUSINESS LOGIC EXACTLY */

        -- Exclude ONLY Gold_Coins (keep BLANK/NULL)

        NOT fact_sales[Cluster] = "Gold_Coins",

        -- Exclude specific Bin Codes (keep BLANK)

        NOT fact_sales[Bin_Code] IN { "TEP", "GEP", "TEP SALE" },

        -- Exclude Pricing Types + NULL

        NOT fact_sales[Pricing_type] IN { "Studded", "UCP", "BLANK", "GOS" },

        NOT ISBLANK ( fact_sales[Pricing_type] ),

        -- Exclude Product Group

        fact_sales[Product_Group] <> "Gold spare"

    )

RETURN

COALESCE (

    ROUND ( SalesAmount / 10000000, 2 ),

    0

)

For this i need share % as i mentioned earlier

so for example here mc band <10% the btq age bad 0-30 days sale is 0.09 and the total sale in btq age band is 2.16 so 0.09/2.16 = 4%
this is the logic.
so i need your help in fixing this.