Drilldown Bar Chart: Ignore a column not shared by related tables

[don_writer]

I don't have an example but the situation should be fairly straight forward.

I have two fact tables:

Active Employees

Terminated Employees

Active Employees (srcActive) has 

EEID

Data_as_of (end of month date)

Division

Age_Bracket

Years of Service

Headcount (0 or 1)

Terminated Employees (srcTerms) has

EEID

Data_as_of (end of month date)

Division

Age_Bracket

Voluntary/Involuntary

Years of Service

Separations (0 or 1)

I have a Date Dimension table (refDate), a Division table(refOrgHeirarchy), Years of Service table, and an Age Bracket Table (refAgeBracket) that join the two fact tables. No Voluntary/Involuntary table because it only appears on the terms table.

I have three measures that calculates the Average Headcount YTD

HeadcountNumerator = CALCULATE(TOTALYTD(sum( srcActive[headcount]),refDate[Date]))

MonthlyDivisor = MAX(srcActive[ActiveMonth])

HeadcountYTDAverage = DIVIDE([HeadcountNumerator],[MonthlyDivisor])

I have a measure that calulates the Total YTD terminations

Separations YTD =TOTALYTD(sum(srcTerms[Separations]),refDate[Date])

And a measure that makes that into Turnover

Turnover = DIVIDE([TermcountTotalYTD],[HeadcountYTDAverage])

All well.

Now I have a Cluster bar chart

Now as you drill through this table each Turnover % is split out by the shared variables appropriately.  However the Voluntary/Involuntary is not shared in the srcActive table. Is there a way to change the headcount formulas to just ignore the voluntary/involuntray column and divide as total year to date average headcount for both voluntary and involuntary but be split out approiately by the other variables?

Thanks for your time.

~Don

[amitchandak]

@don_writer , I think isfiltered hasonevalue and isincope can help in changing formula at different levels.

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[dedelman_clng]

Hi @don_writer  - I've done something similar for HR reports, but I'm not sure what you are or are not seeing in your column chart (or in a table/matrix).  Gave you given an idea of what you are seeing, and what you are wanting to see? Maybe just in table form? I realize that HR data is hard to share while removing sensitive information, so a mockup in Excel or by hand would be OK - I probably have some data I can use on my end.

David