Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 1.  Max calculate based on the slicer

    Posted Oct 26, 2022 06:33 PM
    Edited by Ab Ab Oct 26, 2022 06:39 PM


    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 >


    Ab Ab

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

    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:

    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 Shakir
    Sr. Program Manager, Asset Planning