Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I use the following expression to add the first % to the next and so on:
rangesum(Above(([Sales]/AGGR(NODISTINCT SUM([Sales]),PG)),0, RowNo()))
When i choose another dimension (KC) in addition to PG I would like to keep the same percentages as i had when only PG was chosen but sort out KC.
I tried to add a SET to the expression but the it will only count the visable results.
rangesum(Above(([Sales]/AGGR(NODISTINCT SUM({<KC=>}[Sales]),PG)),0, RowNo()))
I hope my explanation is somewhat understandable.
Try this
RangeSum(Above(([Sales]/Only({<KC>}AGGR(NODISTINCT SUM({<KC=>}[Sales]),PG))), 0, RowNo()))
I tried your suggestion but it didn´t give the answer I was looking for.
The diagram shows all the rows but it doesn´t count the values.
This example, I think, explains how I mean:
Original | ||||
PG | ID | KC | % | Ack % |
HEMACCENT | 1 | IL | 40,0% | 40,0% |
HEMACCENT | 2 | PB | 25,0% | 65,0% |
HEMACCENT | 3 | PB | 20,0% | 85,0% |
HEMACCENT | 4 | IL | 10,0% | 95,0% |
HEMACCENT | 5 | IL | 5,0% | 100,0% |
I would like the expression to do this:
KC=PB | ||||
PG | ID | KC | % | Ack % |
HEMACCENT | 2 | PB | 25,0% | 65,0% |
HEMACCENT | 3 | PB | 20,0% | 85,0% |
KC=IL | ||||
PG | KC | % | Ack % | |
HEMACCENT | 1 | IL | 40,0% | 40,0% |
HEMACCENT | 4 | IL | 10,0% | 95,0% |
HEMACCENT | 5 | IL | 5,0% | 100,0% |
But I get:
KC=PB | ||||
PG | ID | KC | % | Ack % |
HEMACCENT | 2 | PB | 25,0% | 25,0% |
HEMACCENT | 3 | PB | 20,0% | 45,0% |
KC=IL | ||||
PG | ID | KC | % | Ack % |
HEMACCENT | 1 | IL | 40,0% | 40,0% |
HEMACCENT | 4 | IL | 10,0% | 50,0% |
HEMACCENT | 5 | IL | 5,0% | 55,0% |
May be you need this
RangeSum(Above(Only({<KC>}[Sales]), 0, RowNo()))/Sum(TOTAL <PG> [Sales])
Maybe something like this
sum(aggr(rangesum( Above(sum({1}per),0,Rowno(total))),PG,ID))
Thanks for your input but it won´t work for me.
I don´t get the values that I sorted out to add when they are not visible.
Thanks for your input but it won´t work for me.
I don´t get the values that I sorted out to add when they are not visible.
Okay ... not sure how to help without looking at this... more than happy to help if you are able to share a sample
My goal is to make work just like a filter in Excel.
I would like to keep the Ack% for each ID but when i choose KC I get a new value based on the new conditions.
Original table: | ||||
PG | ID | KC | % | Ack % |
HEMACCENT | 1 | IL | 40,0% | 40,0% |
HEMACCENT | 2 | PB | 25,0% | 65,0% |
HEMACCENT | 3 | PB | 20,0% | 85,0% |
HEMACCENT | 4 | IL | 10,0% | 95,0% |
HEMACCENT | 5 | IL | 5,0% | 100,0% |
I would like the expression to do this:
If I choose KC=PB | ||||
PG | ID | KC | % | Ack % |
HEMACCENT | 2 | PB | 25,0% | 65,0% |
HEMACCENT | 3 | PB | 20,0% | 85,0% |
If I choose KC=IL | ||||
PG | KC | % | Ack % | |
HEMACCENT | 1 | IL | 40,0% | 40,0% |
HEMACCENT | 4 | IL | 10,0% | 95,0% |
HEMACCENT | 5 | IL | 5,0% | 100,0% |
But I get:
KC=PB | ||||
PG | ID | KC | % | Ack % |
HEMACCENT | 2 | PB | 25,0% | 25,0% |
HEMACCENT | 3 | PB | 20,0% | 45,0% |
KC=IL | ||||
PG | ID | KC | % | Ack % |
HEMACCENT | 1 | IL | 40,0% | 40,0% |
HEMACCENT | 4 | IL | 10,0% | 50,0% |
HEMACCENT | 5 | IL | 5,0% | 55,0% |
I give up...
Someone else might be able to help you better.
Best,
Sunny