topic Re: Calculate number of sum of values if Entity exists in all the selected years in DAX Commands and Tips

Calculate number of sum of values if Entity exists in all the selected years

AnthonyJoseph — Tue, 17 Jan 2023 08:24:19 GMT

Hello Community,

I am trying to create a measure that calculates sum of values based on a condition (i.e. only it the Entity exists in all of the selected years).
For example: From the below table When the years "2022" and "2021" are selected the calculation/graph should display the sum of values only for "Meat and Fruit" entities (Veggies will not be included as there is no entry for Veggies in 2022). Below are the tables:

Unit table:

Entity	Year	unit
Fruit	2022	100
Fruit	2021	200
Fruit	2020	500
Veggies	2021	100
Veggies	2020	40
Meat	2022	150
Meat	2021	250

Description Table:

Entity	Description	Year	Values
Fruit	Apple	2022	10
Fruit	Pear	2022	20
Fruit	Mango	2022	15
Fruit	Orange	2022	25
Fruit	Grape	2022	30
Fruit	Papaya	2021	10
Fruit	Apple	2021	15
Fruit	Pear	2021	10
Fruit	Mango	2021	15
Fruit	Orange	2021	60
Fruit	Grape	2021	40
Fruit	Kiwi	2021	50
Veggies	Carrot	2021	30
Veggies	Beans	2021	20
Veggies	Cucumber	2021	40
Veggies	Cabbage	2021	10
Veggies	Carrot	2020	10
Veggies	Beans	2020	10
Veggies	Cucumber	2020	10
Veggies	Peas	2020	5
Veggies	Corn	2020	5
Meat	Chicken	2021	50
Meat	Duck	2021	40
Meat	Pork	2021	50
Meat	Turkey	2021	60
Meat	Lamb	2021	50
Meat	Chicken	2022	40
Meat	Goat	2022	20
Meat	Pork	2022	30
Meat	Turkey	2022	60

Expected output:

I m not sure if there is anyway to achieve this but appereciate any response.

Thanks,

AnthonyJoseph

Re: Calculate number of sum of values if Entity exists in all the selected years

ERD — Tue, 17 Jan 2023 09:24:36 GMT

Hello AnthonyJoseph,

I believe there are multiple ways to achieve that. You can try these measures:

ValuesAmt = SUM('Description'[Values])

ValuesFiltered = VAR yearsAmt = COUNTROWS ( ALLSELECTED ( 'Year'[Year] ) ) VAR c_entity = SELECTEDVALUE ( 'Description'[Entity] ) VAR t = FILTER ( Unit, Unit[Entity] = c_entity && Unit[Year] IN ALLSELECTED ( 'Year'[Year] ) ) RETURN IF ( ISFILTERED ( 'Year'[Year] ), IF ( COUNTROWS ( t ) = yearsAmt, [ValuesAmt], BLANK () ), [ValuesAmt] )

Re: Calculate number of sum of values if Entity exists in all the selected years

DimaMD — Tue, 17 Jan 2023 09:28:56 GMT

Hi AnthonyJoseph try it

Amount_value = CALCULATE( SUM('Description Table'[Values]), ALLNOBLANKROW('Description Table'[Year]))

Re: Calculate number of sum of values if Entity exists in all the selected years

DimaMD — Tue, 17 Jan 2023 09:44:50 GMT

Привіт, З Західної України
Слава Україні!!!!

Re: Calculate number of sum of values if Entity exists in all the selected years

AnthonyJoseph — Tue, 17 Jan 2023 14:02:48 GMT

Thanks ERD. When all the years and all entties are selected the graph should display only blank graph but its displaying all the 3 categories. Please can you help me with this.

If you can please attach the pbix it will be easy for me.

Thanks,
AnthonyJoseph

Re: Calculate number of sum of values if Entity exists in all the selected years

AnthonyJoseph — Tue, 17 Jan 2023 14:53:18 GMT

Hi DimaMD I m not able to replicate the output. I tried using the measure. Please if you can share the pbix or provide guidance that will be much helpful.

Thanks,

AnthonyJoseph

Re: Calculate number of sum of values if Entity exists in all the selected years

DimaMD — Tue, 17 Jan 2023 15:43:02 GMT

AnthonyJoseph file

Re: Calculate number of sum of values if Entity exists in all the selected years

ERD — Tue, 17 Jan 2023 15:45:37 GMT

AnthonyJoseph ,

It's easy, just change the return part in the measure:

ValuesFiltered = VAR yearsAmt = COUNTROWS ( ALLSELECTED ( 'Year'[Year] ) ) VAR c_entity = SELECTEDVALUE ( 'Description'[Entity] ) VAR t = FILTER ( Unit, Unit[Entity] = c_entity && Unit[Year] IN ALLSELECTED ( 'Year'[Year] ) ) RETURN IF ( COUNTROWS ( t ) = yearsAmt, [ValuesAmt], BLANK () )

Re: Calculate number of sum of values if Entity exists in all the selected years

AnthonyJoseph — Wed, 18 Jan 2023 06:59:50 GMT

Thanks ERD . Unfortunately the above measure gives blank when more than one year is selected. i.e. when I select 2021 and 2022 the graph should be descriptions that are realted to "Fruits" and "Meat" but its is giving blank.

The request is to provide the list of "Description items" that are common across the selected years (can be of any year selection combination- 2020 and 2021, 2021 and 2022, 2020 and 2022, all three years etc..). Please can you share your thoughts on how to achieve this logic?

Thanks,

AnthonyJoseph

Re: Calculate number of sum of values if Entity exists in all the selected years

ERD — Wed, 18 Jan 2023 07:16:40 GMT

AnthonyJoseph , what table do you use in your slicer? I've added a screenshot showing that there is a dimension table - Year. I used it in a slicer (connected to your data tables). That's why you see blanks.

Re: Calculate number of sum of values if Entity exists in all the selected years

AnthonyJoseph — Wed, 18 Jan 2023 08:18:43 GMT

Thanks for clarifying ERD . However, "Fruit" entity is shown always irrespective of year selection. Also, Blank is not appearing when all years are selected.

I have attached the file here please can you help.

Thanks,

AnthonyJoseph

Re: Calculate number of sum of values if Entity exists in all the selected years

ERD — Wed, 18 Jan 2023 08:51:25 GMT

AnthonyJoseph , that's odd... anyway, we can add a check for a Year slicer: ISFILTERED ( 'Year'[Year] ).

ValuesFiltered = VAR yearsAmt = COUNTROWS ( ALLSELECTED ( 'Year'[Year] ) ) VAR c_entity = SELECTEDVALUE ( 'Description_Table'[Entity] ) VAR t = FILTER ( Unit, Unit[Entity] = c_entity && Unit[Year] IN ALLSELECTED ( 'Year'[Year] ) ) RETURN IF ( ISFILTERED ( 'Year'[Year] ), IF ( COUNTROWS ( t ) = yearsAmt, [ValuesAmt], BLANK () ) )

Re: Calculate number of sum of values if Entity exists in all the selected years

DimaMD — Wed, 18 Jan 2023 10:49:17 GMT

Hi, AnthonyJoseph I corrected the measurement and connections, please see the attachment

Amount_value = VAR _Amount = CALCULATE( SUM('Description Table'[Values]), ALLSELECTED('Yeаr'[Year])) return IF( ISFILTERED('Description Table'[Year]),BLANK(), _Amount)