Is there a way to display in pivot table a subtotal of a power pivot measure only and to skip empty columns?

[anvg]

Hi
Is there a way to display in pivot table a subtotal of a power pivot measure only and to skip empty columns?
Let I want show in that pivot table for a month case - sum of values only and for a year case - count of values only.
For that I create two measures [Month sum only] and [Year count only] with codes

Month sum only:=IF(DISTINCTCOUNT('Calendar'[Month])=1,SUM(Database[Value]),BLANK())

and

Year count:=IF(DISTINCTCOUNT('Calendar'[Month])>1 && DISTINCTCOUNT('Calendar'[Year])=1,COUNT(Database[Value]),BLANK())

If I create a pivot table and use the [Month sum only] measure only then I see what I want. (In my file it is "Month sum only" sheet.
For the [Year count only] measure it is a similar result. ("Year count only" sheet).
But when I create a pivot table with both measures I get empty columns in each months for the [Year count only] measure and empty columns [Month sum only] in each subtotals. ("Both sum and count measure" sheet).
I uploaded my example file to Zippyshare.com - subtotal_only.xlsx
Regards,

 

[anvg]

In Excel 2016 I could edit MDX (Pivot Table tab, Analyse subtab, "Fields and Sets" menu, "Calculation" group, "Create column set" button) which defines what columns of measure will be outtputed. For the pivot table on Both sum and count measure" sheet it is

{
([Calendar].[Year].&[2015],[Calendar].[Month].&[6],[Measures].[Month sum only]),
([Calendar].[Year].&[2015],[Calendar].[Month].&[6],[Measures].[Year count only]),
([Calendar].[Year].&[2015],[Calendar].[Month].&[7],[Measures].[Month sum only]),
([Calendar].[Year].&[2015],[Calendar].[Month].&[7],[Measures].[Year count only]),
([Calendar].[Year].&[2015],[Calendar].[Month].&[8],[Measures].[Month sum only]),
([Calendar].[Year].&[2015],[Calendar].[Month].&[8],[Measures].[Year count only]),
([Calendar].[Year].&[2015],[Calendar].[Month].&[9],[Measures].[Month sum only]),
([Calendar].[Year].&[2015],[Calendar].[Month].&[9],[Measures].[Year count only]),
([Calendar].[Year].&[2015],[Calendar].[Month].&[10],[Measures].[Month sum only]),
([Calendar].[Year].&[2015],[Calendar].[Month].&[10],[Measures].[Year count only]),
([Calendar].[Year].&[2015],[Calendar].[Month].&[11],[Measures].[Month sum only]),
([Calendar].[Year].&[2015],[Calendar].[Month].&[11],[Measures].[Year count only]),
([Calendar].[Year].&[2015],[Calendar].[Month].&[12],[Measures].[Month sum only]),
([Calendar].[Year].&[2015],[Calendar].[Month].&[12],[Measures].[Year count only]),
([Calendar].[Year].&[2015],[Calendar].[Month].[All],[Measures].[Month sum only]),
([Calendar].[Year].&[2015],[Calendar].[Month].[All],[Measures].[Year count only]),
([Calendar].[Year].&[2016],[Calendar].[Month].&[1],[Measures].[Month sum only]),
([Calendar].[Year].&[2016],[Calendar].[Month].&[1],[Measures].[Year count only]),
([Calendar].[Year].&[2016],[Calendar].[Month].&[2],[Measures].[Month sum only]),
([Calendar].[Year].&[2016],[Calendar].[Month].&[2],[Measures].[Year count only]),
([Calendar].[Year].&[2016],[Calendar].[Month].[All],[Measures].[Month sum only]),
([Calendar].[Year].&[2016],[Calendar].[Month].[All],[Measures].[Year count only])
}

I edited it to

{
([Calendar].[Year].&[2015],[Calendar].[Month].&[6],[Measures].[Month sum only]),
([Calendar].[Year].&[2015],[Calendar].[Month].&[7],[Measures].[Month sum only]),
([Calendar].[Year].&[2015],[Calendar].[Month].&[8],[Measures].[Month sum only]),
([Calendar].[Year].&[2015],[Calendar].[Month].&[9],[Measures].[Month sum only]),
([Calendar].[Year].&[2015],[Calendar].[Month].&[10],[Measures].[Month sum only]),
([Calendar].[Year].&[2015],[Calendar].[Month].&[11],[Measures].[Month sum only]),
([Calendar].[Year].&[2015],[Calendar].[Month].&[12],[Measures].[Month sum only]),
([Calendar].[Year].&[2015],[Calendar].[Month].[All],[Measures].[Year count only]),
([Calendar].[Year].&[2016],[Calendar].[Month].&[1],[Measures].[Month sum only]),
([Calendar].[Year].&[2016],[Calendar].[Month].&[2],[Measures].[Month sum only]),
([Calendar].[Year].&[2016],[Calendar].[Month].[All],[Measures].[Year count only])
}

and got needed result
But how to do it automatically?

 

[ImkeF]

Hi anvg,
You could write a combined measure like this:

CombineMonthYear:=IF(DISTINCTCOUNT('Calendar'[Month])=1,SUM(Database[Value]),IF(DISTINCTCOUNT('Calendar'[Month])>1 && DISTINCTCOUNT('Calendar'[Year])=1,COUNT(Database[Value]),BLANK()))

 

[anvg]

Hi, ImkeF
Thank you for your letter and the reply.
In my case my file contains simple example for demonstration my problem.
Really I need to calculate regression and correlation coefficients per years only or for row subtotal and sum for month cells only. That is why I cannot use switching method for measures which you offer. It is not problem to calculate those coefficients but how to show their only in year subtotal?
Regards,

 

[ImkeF]

No practice with that - just the thought already hurts
But actually: Have a look here: Profit & Loss–The Art of the Cascading Subtotal - PowerPivotPro
Probably not the most elegant way today - but that's all I can contribute here.

 

[anvg]

Thank you so much! I went gnaw...

 

[ImkeF]

I know ... pure pain ...

 

[anvg]

Hi
Thank ImkeF again fo your links.
It contains a description about a task which is like your problem in the "Return Filter Names of current Filter Context".
I found how to solve my problem patrially. Thank very much to Chris Webb for his blog and and "Introduction to MDX for PowerPivot Users" post (Introduction to MDX for PowerPivot Users, Part 1 – Chris Webb's BI Blog).
For my task a named column MDX set is

{
([Calendar].[Year].Members,[Calendar].[Month].Members,
[Measures].[Month sum only]),
([Calendar].[Year].Members,[Calendar].[Month].Members,
[Measures].[Year count only]),
([Calendar].[Year].Members,[Calendar].[Month].Members,
[Measures].[Year average only])
}

Maybe it will be interesting for somebody.
Regards,