"Simple" question about filters

[myon87]

Hello

Is there some way to get the total in for 1 to apply for the total of 2 and all its departments? See picture for clarity...

Imgur

 

[Kamolga]

I do not think this is exactly what you want but you can click on your pivot table, go on design tab, subtotal (bottom of the group)


Otherwise you have to refer to your pivot (new sheet with A1=A1, A2=A2,... and A11=A4 or wherever your total is)...if you move to this work around, make sure you inegrate all labels without data in your pivot, so that if department 11, which has 0 hours nows but might get 10 in a year, your pivot does not add a line (department 11 should show 0 now)

 

[scottsen]

The short answer is almost certainly "yes", but what are you actually trying to do?

FreakyHours := CALCULATE([TotalHours], ALL(Dept), MyTable[MyColumn] = 1)

I dunno what you have on rows that is showing "1" and "2"... but that is the MyTable[MyColumn].

 

[myon87]

The short answer is almost certainly "yes", but what are you actually trying to do?

FreakyHours := CALCULATE([TotalHours], ALL(Dept), MyTable[MyColumn] = 1)

I dunno what you have on rows that is showing "1" and "2"... but that is the MyTable[MyColumn].

I know it is funky, but it is what I want to do. Thanks for the replies!

Your suggestion does not work, as in it also overwrites the total hours for Dept in 1 with the total of 1. I just want this to happen for the total for 2 and the Depts in 2.

 

[myon87]

I do not think this is exactly what you want but you can click on your pivot table, go on design tab, subtotal (bottom of the group)


Otherwise you have to refer to your pivot (new sheet with A1=A1, A2=A2,... and A11=A4 or wherever your total is)...if you move to this work around, make sure you inegrate all labels without data in your pivot, so that if department 11, which has 0 hours nows but might get 10 in a year, your pivot does not add a line (department 11 should show 0 now)

Thanks for your reply. This is a solution I think I would have used if I did not have powerpivot. But now I'm trying to use powerpivot.

 

[scottsen]

In your screen shot, what do you have on rows? I see departments, but what is above that? (the 1 and 2).

Cuz, I suspect you will have to throw an IF() around my measure to rest for the 1 vs 2?

 

[myon87]

In your screen shot, what do you have on rows? I see departments, but what is above that? (the 1 and 2).

Cuz, I suspect you will have to throw an IF() around my measure to rest for the 1 vs 2?

1 and 2 represents areas. For example, the 1 area could be departments that do icing on the cake, and the 2 area could be the technicians and quality staff

Should this work in a measure/calc field?

Produced:=IF(dKontering[WEKategori]=1, SUM(fData[Producerat]), CALCULATE(SUM(fData[Producerat]),dKontering[WEKategori]=1,ALL(dKontering[Avdelning])))

Cuz I get an error of circular referencing

Edit: Sorry for the Swedish!

WEKategori = Area
Avdelning = Department

 

[scottsen]

I still feel like we are missing the underlying issue/requirement here. But, to answer your question, no... I wouldn't quite expect the IF() to work.

Measures must always deal with *aggregate* data (so columns need to be wrapped in MIN/MAX() etc). In this case the typical pattern is to use:

IF (HASONEVALUE(dKontering[WEKategori]), 
   IF(VALUES(dKontering[WEKategori])=1,
       ... real stuff ...
   )
)

 

[myon87]

Ok, I think I know what is confusing, and it is the areas. They also seem to have no function in this b/c how pivot tables work.

What I really want to do is to sum produced hours from certain departments and give it as the value for each of the other departments in the pivot table. Illustrative example:

<<<before>>>
Dept 1...10 hours
Dept 2...10 hours
Dept 3...0 hours
Dept 4...4 hours

<<<after>>>
Dept 1...10 hours
Dept 2...10 hours
Dept 3...20 hours
Dept 4...20 hours

Is this helping at all?</after></before>