rolling up in calculated column

[Anonymous]

Hello All,

I have a dataset like this.

Recruiter Month         Login

A              JAN2018      10

B               JAN2018      15

A               FEB2018       20

I have a calculated column called [Login Level] which has values 0-5,5-10,10-15,15+ and in my report this calculated column is one of the filter and month is also a filter. WHen I select one month in the filter and one login level this works correctly, but when I dont filter data based on month and select 1 filter value for login level, this does not work,how can I make sure the login level calculated column is calculated only after aggregating the monthly login count in case I do not have any filter on my month ?

Thanks in advance,

Nikesh

[v-juanli-msft · Accepted solution]

Hi @Anonymous

your formula [Login Level] calculate for each row.

why you see the problem is because there is no "month" information in the column chart, so each column is aggregated by the "recruiter" items.

When you add "month" in the "legend" field, it will filter only the selected login activity levevl.

I'm not very clear about 

"login avtivity levevl needs to be calculated after aggragating the login count and then segragate into different categories"

could you show me example?

Also, does my test meet your need?

create a measure to aggragate the login count for each "recruiter" among all months.

Measure = CALCULATE(SUM(Sheet17[unique daily login]),ALLEXCEPT(Sheet17,Sheet17[recruiter]))

Then create "Login Level" based on the measure

Column 2 = IF([Measure]<6,"0~5",IF([Measure]<11,"5~10",IF([Measure]<15,"10~15","15+")))

 

best reagrds

Maggie

[Anonymous]

Thanks Maggie, that is pretty close.

I tried to change the measure formula as below.

Measure = CALCULATE(

SUM(Sheet17[unique daily login]),

IF(ISFILTERED(Sheet17[month]),Sheet17[month],ALL(Sheet17[month]))

)

But this script is giving me an error.

Idea is when the month filter is on, no need to aggegate across months, but when month is not filtered, it has to be aggragated.

Thanks,

Nikesh

[v-juanli-msft]

Hi @Anonymous

Per your requirement,

create a new table by enter data. don't connect this table to your data table

create measures in your data table,

selected = SELECTEDVALUE(Table2[slicer])

month filtered = IF(ISFILTERED(Sheet17[month]),1,0)

total all month per recruiter = CALCULATE(SUM(Sheet17[unique daily login]),ALLEXCEPT(Sheet17,Sheet17[recruiter]))

show as value = IF([month filtered]=1,MAX([unique daily login]),[total all month per recruiter])

which to show = IF([show as value]<MAX(Table2[max])&&[show as value]>=MAX(Table2[min]),1,0)

Then add [show as value] in the Value field of the column chart, add [which to show] in the Visual level filter and set "show items when value is 1".

Best Regards

Maggie

[v-juanli-msft]

Hi @Anonymous

I don't understand your requirement.

"WHen I select one month in the filter and one login level this works correctly, but when I dont filter data based on month and select 1 filter value for login level, this does not work,"

Could you show an example of this?

i create a sample pbix, but i don't know what's the difference between filtering in month and login level or only filter in login level.

Please create an example in my pbix or show a screenshot of you pbix about the difference of them.

How do calculate the login level calculated column?

Could you show the formula here?

What does this mean?

"the login level calculated column is calculated only after aggregating the monthly login count in case I do not have any filter on my month "

Best Regards

Maggie

[Anonymous]

Hello,

Thanks for your reply, I have attached my sample pbix. Here in step 1, please select a month in the slicer and a login activity levevl too. In the bar, all the login activity level will be in the same range. Suppose if you select login activity levevl as  0-5 , all the bars will be in the range 0-5. Now if you dont select any month in the filter(all months are chosen) and select the same login activity level 0-5, all the  bars will not be in the range 0-5, but it will show the aggregated data for all months. How can we make sure if its not filtered by month, login avtivity levevl needs to be calculated after aggragating the login count and then segragate into different categories. 

Thanks,

Nikesh

[Greg_Deckler]

You probably need to use a measure versus a calculated column.

[Anonymous]

Thanks Greg, but this field has to be a slicer for my report, I am not quite sure if I can use a measure as a slicer?

Thnaks,

Nikesh