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

    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
    ------------------------------


  • 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:
    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)