Forum Discussion
Share % Measure Logic tweak help
- 4 months ago
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.
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!
- GanesaMoorthyGM4 months agoHelper II
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_sapna4 months agoSuper User
- GanesaMoorthyGM4 months agoHelper II
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_BandValues: 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")RETURNCOALESCE ( 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 + NULLNOT fact_sales[Pricing_type] IN { "Studded", "UCP", "BLANK", "GOS" },NOT ISBLANK ( fact_sales[Pricing_type] ),-- Exclude Product Groupfact_sales[Product_Group] <> "Gold spare")RETURNCOALESCE (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.