Forum Discussion

Lanceometer's avatar
Lanceometer
Helper I
1 year ago
Solved

Visual Calculation using FIRST()

Hi Folks,

 

I have data for some suppliers and prices for a material:

Supplier Date NetPrice
SUP1 28.02.2024 20,66
SUP1 29.02.2024 20,66
SUP2 01.03.2024 16,95
SUP2 02.03.2024 16,95
SUP2 03.03.2024 16,95
SUP2 04.03.2024 16,95
SUP2 05.03.2024 19
SUP2 06.03.2024 19

 

I want to show where the price was going up or down per supplier.

 

The user wants to see this in an indexed line chart

something like this:

you can see that also the supplier changed

 

so it is basically calculated 

Calculation = [Average of NetPrice] / FIRST([Average of NetPrice])
 
But when I do this in a visual calculation it doesn't work. Apperantly FIRST() does not respect the legend (even with the parameters for sorting blank values last). So it takes the first date of all suppliers. but for my second supplier the first value is then blank. this is why i get infinity values
 

 

Any ideas on that?

DAX
FIRST
Visual Calculations
  • ValtteriN's avatar
    ValtteriN
    1 year ago

    Hi,

    As seen in the visual calculation table supplier appears twice in your filter context. Because of this it is easier not to use visual calculations in this case, since calculating first date for a specific supplier will be challenging. With normal dax you can e.g. create something like this:

    first net price division =
    var _s = SELECTEDVALUE('Table (47)'[Supplier])
      VAR _firstDate = CALCULATE(MIN('Table (47)'[Date]),ALL('Table (47)'[Date]),'Table (47)'[Supplier]=_s)
      RETURN
      DIVIDE([Average of NetPrice],
    CALCULATE(
    [Average of NetPrice],ALL('Table (47)'[Date]),'Table (47)'[Date]=_firstDate))

     

    I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

    My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/




1 Reply

  • ValtteriN's avatar
    ValtteriN
    Community Champion
    1 year ago

    Hi,

    As seen in the visual calculation table supplier appears twice in your filter context. Because of this it is easier not to use visual calculations in this case, since calculating first date for a specific supplier will be challenging. With normal dax you can e.g. create something like this:

    first net price division =
    var _s = SELECTEDVALUE('Table (47)'[Supplier])
      VAR _firstDate = CALCULATE(MIN('Table (47)'[Date]),ALL('Table (47)'[Date]),'Table (47)'[Supplier]=_s)
      RETURN
      DIVIDE([Average of NetPrice],
    CALCULATE(
    [Average of NetPrice],ALL('Table (47)'[Date]),'Table (47)'[Date]=_firstDate))

     

    I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

    My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/