Hi, I struggle with ALLEXCEPT in one of my measures, here is what I want:
Example table "Apples":
Year | Color | Size | Amount |
2007 | green | big | 10 |
2007 | red | small | 20 |
2007 | red | big | 20 |
2008 | green | big | 10 |
2008 | green | small | 10 |
2008 | red | big | 20 |
2008 | red | small | 20 |
2009 | green | big | 10 |
2009 | yellow | small | 10 |
2009 | red | small | 20 |
2010 | green | small | 10 |
| |
I like to visualise on a line and clustered column chart, "allApples" and "selectedApples" side by side with line representing the ratio of "allApples" to "selectedApples" (="ratio"); x-axis are the Years. "selectedApples" is the the sum of Amount per Year, which should react to a slicer where I can choose in two slicers Color and Size. "allApples" are the sum of Amount per Year without reacting to the slicer. But the one yellow apple should be filtered out by an page level filter.
My solution attempt:
allApples=Calculate(sum(Apples[Amount]);Allexcept(Apples;Apples[Year]);Filter(Apples;Apples[Color]<>"yellow"))
selectedApples= sum(Apples[Amount])
ratio=Apples[selectedApples]/Apples[allApples]
remark: the orginaal table got more columns, data entries and slicer. This is just a simplisitc example
Here is how it turns out: The slicer for Color affects also allApples. Why?? Now I would assume that is related to the filter in "allApples" measure, but the slicer for Size does also affect "allApples"
A not sufficient Solution:
when I incorporate the ALLEXCEPT in a filter: allApples=Calculate(sum(Apples[Amount]);Filter(Allexcept(Apples;[Year]);Apples[Color]<>"yellow")) it seems to work.. but: when I choose only red apples in the slicer, the data for 2010 is not displayed for "allApples" because "selectedApples" does not have any data. How to prevent this?????????? I choose already "show items with no data" with the x-axis..
PLEASE HELP!! and Thank you in advance, I think I miss here somthing obvious :((
