Max calculate based on the slicer

Max calculate based on the slicerJump to Best AnswerFollow
Ab Ab
Ab AbOct 26, 2022 06:33 PM
Hello, I have two tables are sale and Month. In Month table contains item, month and qty and Month …
1. Max calculate based on the slicer

Ab Ab
Posted Oct 26, 2022 06:33 PM
Edited by Ab Ab Oct 26, 2022 06:39 PM
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

If I selected the Jun and Jul in slicer, then it will show sum of sale qty
If I selected all of them, then it will show sum of sale qty
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
——————————

2. RE: Max calculate based on the slicer Best Answer

Kaz Shakir
Posted Oct 29, 2022 07:31 PM | view attached
@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
——————————

Attachment(s)

pbixMAX SLICER-26-10-2022_r2.pbix