Power BI Exchange

Hello,

I have two tables are sale and Month.

 In Month table contains item, month and qty and Month table has Month.

 I added month table in slicer, I want to see sum of sale qty based on the sale qty and price in visualization table depends on the month selection in slicer.

I created calculate column but, I like to create one measure based on the Jun, Jul and Aug, so I can filter the result in visual.

For example –

 If I selected the Jun in slicer, then it will show sum of sale qty

1. If I selected the Jun and Jul in slicer, then it will show sum of sale qty
2. If I selected all of them, then it will show sum of sale qty
3. If I deselected any one of the moths from slicer, then it will show relevant sum of sale qty.
   MAX SLICER-26-10-2022.pbix

   Dropbox		remove preview
   MAX SLICER-26-10-2022.pbix Dropbox is a free service that lets you bring your photos, docs, and videos anywhere and share them easily. Never email yourself a file again! View this on Dropbox >

   
   
   

https://www.dropbox.com/s/8ru5ax3wwdvl6z0/MAX%20SLICER-26-10-2022.pbix?dl=0

------------------------------
Ab Ab
Engineering
------------------------------

@Ab Ab,
For this one, you need to perform some data transformation.  You have to get your SALE table to be "tall and thin" - this is done by "unpivoting" the table in the Transform Data section of Power BI.  Also, I would remove the prices from this table, and create a separate prices table.  After you transform the SALE table it will look like this:
​

And your PRICES table should look like this:

With those tables created, then you can create a data model that looks like this:

And finally, create a "Sales Amount" measure that looks like this:

Sales Amount = SUMX(SALES, SALES[QTY] * RELATED(PRICES[PRICE]))​

Then, a matrix visual using the "LIST" column from your MONTH table, and the "Sales Amount" measure should give you the results you want:


Please let us know if that's what you wanted to achieve.

Kaz.

------------------------------
Kaz Shakir
Sr. Program Manager, Asset Planning
TN
------------------------------

Original Message:
Sent: Oct 26, 2022 06:32 PM
From: Ab Ab
Subject: Max calculate based on the slicer

Hello,

I have two tables are sale and Month.

 In Month table contains item, month and qty and Month table has Month.

 I added month table in slicer, I want to see sum of sale qty based on the sale qty and price in visualization table depends on the month selection in slicer.

I created calculate column but, I like to create one measure based on the Jun, Jul and Aug, so I can filter the result in visual.

For example –

 If I selected the Jun in slicer, then it will show sum of sale qty

1. If I selected the Jun and Jul in slicer, then it will show sum of sale qty
2. If I selected all of them, then it will show sum of sale qty
3. If I deselected any one of the moths from slicer, then it will show relevant sum of sale qty.
   MAX SLICER-26-10-2022.pbix

   Dropbox		remove preview
   MAX SLICER-26-10-2022.pbix Dropbox is a free service that lets you bring your photos, docs, and videos anywhere and share them easily. Never email yourself a file again! View this on Dropbox >

   
   
   

https://www.dropbox.com/s/8ru5ax3wwdvl6z0/MAX%20SLICER-26-10-2022.pbix?dl=0

------------------------------
Ab Ab
Engineering
------------------------------