Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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
Solved! Go to Solution.
@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 () )
)
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
Hi @AnthonyJoseph try it
Amount_value =
CALCULATE( SUM('Description Table'[Values]), ALLNOBLANKROW('Description Table'[Year]))
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
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)
@AnthonyJoseph file
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]
)
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
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
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 () )
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
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
@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.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
@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 () )
)
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
Привіт, З Західної України
Слава Україні!!!!
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
9 | |
8 | |
7 | |
6 |
User | Count |
---|---|
14 | |
13 | |
11 | |
9 | |
8 |