Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Calculate measure based on corresponding value multiply by another value based on slicer

I have a data with 4 columns say Item, Year, Price & Quantity. I have 2 slicers (Reference Year & Item). Now I need to calculate 'Item_Value' and show year wise on coumn chart where 'Item_Value' is corresponding Price multiplied by Reference Year quantity.  Reference year slicer is only sinlge select and user need to select it. For example if user select Reference year(2020) then each item price for each year need to multiply by quantity of (2020) and total need to display in chart year wise. 

ItemYearpricequantity 

Value

when (ref year selected 2021)

Ref Year SlicerItem Slicer
12019530 =5*502021 
12020640 =6*50  
12021450 =4*50YearTotal Value ( Corresponding Year price * Selected Year Quantity)
22019560 =5*5520195*50+5*55=525
22020645 =6*5520206*50+6*55=630
22021555 =5*5520214*50+5*55=475
Need Help
  • v-angzheng-msft's avatar
    v-angzheng-msft
    5 years ago

    Hi, Anonymous 

     

    This all seems to work well for me, please check my attachment. Note the use of the year field for the main table

    This seems to work well for me, please check my attachment. Note the use of the year field of the main table.
    If yours does not work, please show screenshots or sample file so that I can find a solution faster.

     

    Best Regards,
    Community Support Team _ Zeon Zheng
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi, Anonymous 

     

    I create a measure to new a calculated table with Ref year:

     

    Ref Year = SUMMARIZE('Table','Table'[Year])

     

    measure of Item Value

     

    _itemValue = 
VAR _refQT =
    CALCULATE (
        SUM ( 'Table'[quantity] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Item] ),
            'Table'[Year] = ALLSELECTED ( 'Ref Year'[Ref Year] )
        )
    )
RETURN
    MAX ( 'Table'[price] ) * _refQT

     

    I created another measure to dynamically display the selected year on the title

     

    Ref Year SlicerItem Slicer = CONCATENATE("Ref Year Slicer: ",FORMAT(ALLSELECTED('Ref Year'[Ref Year]),"General Number"))

     

    Total value:

     

    TotalValue = CALCULATE(SUMX(ALLEXCEPT('Table','Table'[Year]),[_itemValue]))

     

    Result:

    Please refer to the attachment below for details

     

     

    Hope this helps.

     

    Best Regards,
    Community Support Team _ Zeon Zheng
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Zeon,

    Thanks for the reply. This way I can find the values itemwise in table visual but its not working when I am trying to create a column chart with Year( on X axis) and total values. I want to display year on year total value based on the reference year quantities multiply by base year price.

    • v-angzheng-msft's avatar
      v-angzheng-msft
      Icon for Community Support rankCommunity Support
      5 years ago

      Hi, Anonymous 

       

      This all seems to work well for me, please check my attachment. Note the use of the year field for the main table

      This seems to work well for me, please check my attachment. Note the use of the year field of the main table.
      If yours does not work, please show screenshots or sample file so that I can find a solution faster.

       

      Best Regards,
      Community Support Team _ Zeon Zheng
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Thanks Zeon.. it worked 🙏