I have a matrix with currently 4 rows in a hierarchy to drill down on.
I have crated the following DAX to show the correct % at each level of the hierarchy.
Count No Documents = DIVIDE(
SUM(‘Vendors Invoices Cockpit’[R.101.Nbr of documents]),
CALCULATE(
SUM(‘Vendors Invoices Cockpit’[R.101.Nbr of documents]),
ALL(‘223 Key’[223.Doc Name])
)
)
When i try to present this as a % dynamically the % values dont work as while the % breakdown works on the lowest level the higher levels all shows as 100% not a % of their hierarchy as per the 2nd attached picture.
I was able to fix this with the following DAX and show in the first picture.
However i was wondering if there was a more simplistic way to do this and for it to be dynamic if i change items in the hierarchy in the future
% of Docs (Hierarchical) =
SWITCH(
POWER(2,0) * ISFILTERED(‘Vendors Invoices Cockpit’[Country.Country Level 01]) +
POWER(2,1) * ISFILTERED(‘Vendors Invoices Cockpit’[Entity.Entity Level 01])+
POWER(2,2) * ISFILTERED(‘Vendors Invoices Cockpit’[Company code.Company code Level 01]) +
POWER(2,3) * ISFILTERED(‘223 Key’[223.Doc Name]),
1,
DIVIDE(
SUM(‘Vendors Invoices Cockpit’[R.101.Nbr of documents]),
CALCULATE(
SUM(‘Vendors Invoices Cockpit’[R.101.Nbr of documents]),
ALL(‘Vendors Invoices Cockpit’[Country.Country Level 01])
)
),
3,
DIVIDE(
SUM(‘Vendors Invoices Cockpit’[R.101.Nbr of documents]),
CALCULATE(
SUM(‘Vendors Invoices Cockpit’[R.101.Nbr of documents]),
ALL(
‘Vendors Invoices Cockpit’[Entity.Entity Level 01]
)
)
),
7,
DIVIDE(
SUM(‘Vendors Invoices Cockpit’[R.101.Nbr of documents]),
CALCULATE(
SUM(‘Vendors Invoices Cockpit’[R.101.Nbr of documents]),
ALL(
‘Vendors Invoices Cockpit’[Company code.Company code Level 01]
)
)
),
15,
DIVIDE(
SUM(‘Vendors Invoices Cockpit’[R.101.Nbr of documents]),
CALCULATE(
SUM(‘Vendors Invoices Cockpit’[R.101.Nbr of documents]),
ALL(‘223 Key’[223.Doc Name])
)
),
IF(SUM(‘Vendors Invoices Cockpit’[R.101.Nbr of documents]) >0,“N/A”, BLANK())
)
