Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have this measure in a pivot table and when the filter pane is selected for an individual month I get the correct percentage, however once I have more than one month selected in the filter pane the pivot table percentage decreases.
Count({$<[Movie]=>}Distinct[TicketNo.])/Count(Total{$<[Movie]=>}Distinct[TicketNo.])
Can you help me modify this measure so that whether an individual month or multiple months are selected in the filter pane the pivot table percentage stays the same?
can you share screen shot of your output??
before selection and after selection.
take both expression i.e. numerator and denomenator in 2 KPI object check what was value of it before and after selection.
Not sure it will help you or not but you can try below expression.
Count({$<[Movie]=>}Distinct[TicketNo.])/Count(Total<Month> {$<[Movie]=>}Distinct[TicketNo.])
Regards,
What is in your pivot table ?
Hi Leo,
I think its happening because you are using Total and $ in set analysis in your divider expression. Try using {1} instead of $ will get your desired result. Read this link for help.
When multiple months are selected , which month data you want to show in pivot?
You can put that month in set analysis
for instance, if you want to show it for max month, use like this:
Count({$<[Movie]=,MONTH = {$(=max(MONTH))}>}Distinct[TicketNo.])/Count(Total{$<[Movie]=>}Distinct[TicketNo.])
I want the percentages to remain unchanged regardless of the month(s) selected in the filter pane
If Dec, Nov, Oct, or Sep is selected in the filter pane individually they display the percentages in the pivot table below.
Movie | Dec 2016 |
Action | 87.0% |
Comedy | 13.0% |
Movie | Nov 2016 |
Action | 83.4% |
Comedy | 16.6% |
Movie | Oct 2016 |
Action | 86.5% |
Comedy | 13.5% |
Movie | Sep 2016 |
Action | 83.5% |
Comedy | 16.5% |
However if the filter pane does not have a month selected the percentages above will change to the percentages below.
Movie | Dec 2016 | Nov 2016 | Oct 2016 | Sep 2016 |
Action | 19.9% | 22.5% | 20.8% | 21.8% |
Comedy | 3.0% | 4.5% | 3.2% | 4.3% |
Regardless of the month or months selected in the filter pane I want the percentages to remain unchanged like in the table below
Movie | Dec 2016 | Nov 2016 | Oct 2016 | Sep 2016 |
Action | 87.0% | 83.4% | 86.5% | 83.5% |
Comedy | 13.0% | 16.6% | 13.5% | 16.5% |
If this syntax...
aggr(nodistinct count([Fieldname?]),[MonthYear])
can be successfully incorporated into the denominator in the expression below...
Count({$<[Movie]=>}Distinct[TicketNo.])/Count(Total{$<[Movie]=>}Distinct[TicketNo.])
I think percentages will remain unchanged regardless of the month(s) selected in the filter pane. Any thoughts?
can you share screen shot of your output??
before selection and after selection.
take both expression i.e. numerator and denomenator in 2 KPI object check what was value of it before and after selection.
Not sure it will help you or not but you can try below expression.
Count({$<[Movie]=>}Distinct[TicketNo.])/Count(Total<Month> {$<[Movie]=>}Distinct[TicketNo.])
Regards,