Help with Calculate arguments

Anonymous · ‎06-14-2018

I have a table visual with a measure, and one of the columns in the visual needs to be ignored in caulcating the measure. I can use the ALL argument, but then it also ignores a slicer I have on the report (because they are consuming the same table column)

Is there a way to force the measure to ignore the column in the visual, but not the slicer...?

v-juanli-msft · ‎06-14-2018

Hi @Anonymous

Try to use ALLEXCEPT instead of ALL, put the column "a" which is used in a slicer to the ALLEXCEPT like this "ALLEXCEPT(table,table[a])

Best Regards

Maggie

Anonymous · ‎06-15-2018

Unfortunately, that doesn't work.

Data Table:

ID	Age	Country
1	33	Canada
2	44	USA
3	55	USA
4	43	Canada
5	54	USA
6	75	America
7	12	America
8	47	Canada
9	23	USa
10	38	Canada

Mapping Table

Country	Mapped
Canada	Canada
USA	USA
America	USA

There is a relationship between Country for both tables. I have one measure: Measure = Average(Sheet1[Age])

Here is the Table Visual with the measure as is:

So I adjust the measure to be: Measure = CALCULATE(Average(Sheet1[Age]),all(Sheet1[ID]))

Now it looks likes this:

You can see how it now shows duplicates for every value in the mapping table, and the average age is for for mapped country. So I try: Measure = CALCULATE(Average(Sheet1[Age]),allexcept(Sheet1,Sheet1[Country]))

The average age is correct, but the duplicates are still there.

jthomson · ‎06-15-2018

What's your desired output? It looks like the measure is working

Anonymous · ‎06-15-2018

If you look at the last visual, it gives the impression that ID 1 is in both countries. I want to display the average of all ages in the data, regardless of country, but I also want the report user to be able to see what country they ID belongs to. I just can't seem to make it work.

My second requirement, which adds complexity to all this, is a slicer FOR country. When Canada is selected, the table visual will ONLY show the average age for Canada, and will ONLY show Canadian IDs. When both countries are selected, it will take the average across both countries and show IDs in both countries.

I get that i can use a 2nd visual for the average, but this is just a simplified example to illustrate my problem. I have to have the measure in the table.