Hi ,
I’m trying in vain to formulate a DAX formula that will show me Category shares and within that Brand shares. I have a matrix, and in the rows I have category (Sub family name in the file) and in the columns I have Brand, so I need to show by rows the Brand share within that category and at the end it should show the Category weight. Also, at the end of the column it should show the Brand weight of business among all Brands. Is this possible to show in a matrix.
Appreciate if someone can show me a workaround. Below is the file.
EPOS.pbix (766.8 KB)
is this what you are looking for
EPOS.pbix (766.8 KB)
I used 3 measures
m_SalesBrandPercentage
var TotalSales = SUMX(ALLSELECTED(EPOS[Brand]),
[Total Sales])
RETURN
DIVIDE
([Total Sales], TotalSales)
m_SalesBrandSubFamilyPercentage
var TotalSubFamilySales = SUMX(ALLSELECTED(EPOS[Sub Family Name]),
[Total Sales])
RETURN
m_FinalMeasure =
if(ISINSCOPE(EPOS[Sub Family Name]), [m_SalesBrandSubFamilyPercentage], [m_SalesBrandPercentage])
DIVIDE
([Total Sales], TotalSubFamilySales)
Hi, thanks for helping. I checked the file, and input your formulas, however the subfamily shares are correct but the Brands shares are not correct. I am attaching the xl file as well if you can check for Braun in 2019, the hand blender shares were 32.4 % where as in the BI it shows as 49.67%. Hope you can work this out.EPOS.xlsx (3.7 MB) EPOS.pbix (764.3 KB)
reattaching the pbix file with the updated code
m_FinalMeasure
if(ISINSCOPE(EPOS[Brand]), [m_SalesBrandPercentage], [m_SalesBrandSubFamilyPercentage])
the IsInScope function should check for the lowest level in the hierarchy first. so i changed the if condition to check for Brand before the subfamily name…
let me know if this works out better for you
EPOS.pbix (763.7 KB)
Yes, its perfect now. No more sleepless nights on this.
Thank you, and will check out the IsInScope function .
Much Appreciated.
