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

anvg

Active Member
Joined
Feb 14, 2012
Messages
485
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
Code:
Month sum only:=IF(DISTINCTCOUNT('Calendar'[Month])=1,SUM(Database[Value]),BLANK())
and
Code:
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,
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

anvg

Active Member
Joined
Feb 14, 2012
Messages
485
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
Code:
{
([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
Code:
{
([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

Well-known Member
Joined
Feb 1, 2015
Messages
544
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

Active Member
Joined
Feb 14, 2012
Messages
485
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,
 

anvg

Active Member
Joined
Feb 14, 2012
Messages
485
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
Code:
{
([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,
 

Watch MrExcel Video

Forum statistics

Threads
1,122,224
Messages
5,594,912
Members
413,952
Latest member
JGer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top