cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Nuno_Paixao
Regular Visitor

Add Sumif values from a different table based on slicer selection

Hi,

 

I would like to fulfil a column with a sumif of values from a different table but with a filter from a slicer.

 

Let’s give the example:

  • One table has a list of items with two columns (item number and Item description)

Nuno_Paixao_1-1664694638686.png

  • Other table has the consumption of items (item number, quantity and consumption date

Nuno_Paixao_3-1664695003217.png

My objective is, keeping the first list with all items and add one more column to fulfil with the consumption for respective item.

But the consumption must change for the respective year selected on slicer with the year.

 

For example, if 2021 is selected on slicer, the item, in the first table the item "a" will appear with the value 2 on column consumption, all the other items will keep with no valeu or zero value.

If 2022 was selected on slicer, the item, in the first table the item "a" will appear with the valeu 4 on column consumption and item "c" with 14, all the other items will keep with no valeu or zero value.

 

Many Thanks 

Nuno Paixão

1 ACCEPTED SOLUTION

@Nuno_Paixao 

Hi,

Right click the Item then tick show items with no data.

Dinesh_Suranga_0-1664708161758.png

Please accept this as solution if this helped to solve your problem.

Thank you

 

View solution in original post

4 REPLIES 4
Nuno_Paixao
Regular Visitor

Hi Dinesh,

 

many thanks, so simple.

 

KR 

Nuno Paixão

Dinesh_Suranga
Responsive Resident
Responsive Resident

@Nuno_Paixao 

First -  Create relationship between items table and consumption table base on Item.

Dinesh_Suranga_0-1664697630206.png

 

Then create a mesure using  following DAX formula.

Consumption = SUM(Consumptions[qtd])

Then you will get the answer.

Dinesh_Suranga_1-1664697901921.png

Dinesh_Suranga_2-1664697924435.png

Please post back here if you are having issues.

If this post helped to solve your issue. Please acept as the solution.

Thank you.

Hi Dinesh,

 

Thanks, but I need keep the other items in the list with zero values.

 

Many Thanks

Nuno Paixão

 

@Nuno_Paixao 

Hi,

Right click the Item then tick show items with no data.

Dinesh_Suranga_0-1664708161758.png

Please accept this as solution if this helped to solve your problem.

Thank you

 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.