topic Re: Slicer for Per Day and Consumer on 2 different Tables in DAX Commands and Tips

Slicer for Per Day and Consumer on 2 different Tables

HumanX — Sun, 03 Dec 2023 09:54:29 GMT

Hi,

I'm new to power bi and want to ask help with slicer using 2 tables

Consumer Goods Table

Consumer	day1	day 2	day 3
Consumer 1	1	2	3
Consumer 2	2	4	5

Consumer Sales Table

Consumer	day1	day 2	day 3
Consumer 1	22	22	223
Consumer 2	22	44	55

now i want to have 2 slicer and 2 cards: date and Consumer so i can display the result on 2 cards containing Sales and goods average. (per day average or individual data of consumer)

Note:
Currently i have 4 slicer which is 2 for goods table and 2 slicer for sales table, i just want to combine and if possible there will be 2 slicer since they have same column and consumer.

Any idea will help. Thanks!

Re: Slicer for Per Day and Consumer on 2 different Tables

Fowmy — Sun, 03 Dec 2023 10:06:16 GMT

HumanX

FIrstly, you need unpivot your data to have three columns for each table as Consumer, Goods , Date and Consumer, Sales and Date. Then the rest will be easy for to do the calcualtion.

Follow these videos if you need to know how to unpivot:
https://www.youtube.com/watch?v=Vff2kRBM95o&pp=ygUJZXhjZWxmb3J0

Re: Slicer for Per Day and Consumer on 2 different Tables

HumanX — Sun, 03 Dec 2023 10:16:45 GMT

Hi,

Yes I already did that and for my 3rd table I used this

My TBL =

UNION(

SUMMARIZE(

Consumer Goods Table,

Consumer Goods Table[Consumer],

Consumer Goods Table[Count per day]

SUMMARIZE(

Consumer SalesTable,

Consumer SalesTable[Consumer],

Consumer SalesTable[Count per day]

)

)

now when I created a measure inside MY TBL

Average Goods = AVERAGE(Consumer Goods Table[Values])

Average Sales= AVERAGE(Consumer SalesTable[Values])

and i put it on my card, it shows wrong computation as I don't know how its been computed since i just want to average values per day of all the consumer. Thank you in advance!

Re: Slicer for Per Day and Consumer on 2 different Tables

Fowmy — Sun, 03 Dec 2023 11:33:17 GMT

HumanX

Not clear, sorry.

Please share your PBI file, I hope it contains dummy data.
Save it in Google Drive and share the link here and ensure access is given

Re: Slicer for Per Day and Consumer on 2 different Tables

HumanX — Sun, 03 Dec 2023 12:28:59 GMT

Hello,

Here's the PBI file
https://drive.google.com/file/d/1gh2IWg7K9HUQt-Zjyc8DrZklUrihwjxM/view?usp=sharinghttps://drive.google.com/file/d/1gh2IWg7K9HUQt-Zjyc8DrZklUrihwjxM/view?usp=sharing

also the Excel file if needed
https://docs.google.com/spreadsheets/d/1T13S5NJynvsDTCR5a40nG7GPkikRMYVf/edit?usp=drive_link&ouid=117986704807984671965&rtpof=true&sd=true