Forum Discussion

lafakios's avatar
lafakios
Icon for Helper I rankHelper I
1 year ago

Average of the last 3 per param = {ItemA 1.3(param.value) + ItemA 1.2(param.value) + ItemA 1.1(param.value)} / 3

 

example: Average of the last 3 of param1 = {1 + 2 + 3} / 3 = 2

 

HigherOrLowerThanAverage per param = If( ItemA 1.4(param.value) >= Average of the last 3 per param, "H", "L")

 

example: HigherOrLowerThanAverage of param1 = L {0 < 2}

ryan_mayu's avatar
ryan_mayu
Icon for Super User rankSuper User
1 year ago

where is the item 1.4 data? I can't see in it in your sample data.

  • lafakios's avatar
    lafakios
    Icon for Helper I rankHelper I
    1 year ago

    The itemA 1.4 data were missing in the sample table.

    Please find below the updated sample table including itemA 1.4

     

    Item_IDparam_idvalue
    ItemA 1.4 Param10
    ItemA 1.4 Param29
    ItemA 1.4 Param31
    ItemA 1.4 Param46
    ItemA 1.4 Param530
    ItemA 1.3 Param11
    ItemA 1.3 Param212
    ItemA 1.3 Param32
    ItemA 1.3 Param43
    ItemA 1.3 Param520
    ItemA 1.2Param12
    ItemA 1.2Param214
    ItemA 1.2Param36
    ItemA 1.2Param43
    ItemA 1.2Param510
    ItemA 1.1Param13
    ItemA 1.1Param216
    ItemA 1.1Param34
    ItemA 1.1Param44
    ItemA 1.1Param530
    ItemA 1.0Param15
    ItemA 1.0Param212
    ItemA 1.0Param37
    ItemA 1.0Param412
    ItemA 1.0Param514
    ItemB 1.3 Param120
    ItemB 1.3 Param22
    ItemB 1.3 Param314
    ItemB 1.3 Param46
    ItemB 1.3 Param53
    ItemB 1.2Param12
    ItemB 1.2Param214
    ItemB 1.2Param36
    ItemB 1.2Param43
    ItemB 1.2Param510
    ItemB 1.1Param13
    ItemB 1.1Param216
    ItemB 1.1Param34
    ItemB 1.1Param44
    ItemB 1.1Param530
    ItemB 1.0Param116
    ItemB 1.0Param24
    ItemB 1.0Param34
    ItemB 1.0Param430
    ItemB 1.0Param55
  • lafakios's avatar
    lafakios
    Icon for Helper I rankHelper I
    1 year ago

    Thank you for building a PBIx report and sharing it, this actually does provide the needed functionality!

     

    One of the key calculated columns is the below:

    Column = CALCULATE(DISTINCTCOUNT('Table'[Item_ID]),FILTER('Table','Table'[param_id]=EARLIER('Table'[param_id])&&'Table'[item]=EARLIER('Table'[item])&&'Table'[Item_ID]>=EARLIER('Table'[Item_ID])))
     
    I did a quick check if the above logic can scale to more than 4 items e.g. to support also items like itemA 1.5, 1.6, 1.7 etc. and the check result was successful.
     
    One minor drawback of the current solution is that the column header in the visual refers to a measure name and not to the actual item represented by the measure e.g. itemA 1.4, 1.3, 1.2
    • lafakios's avatar
      lafakios
      Icon for Helper I rankHelper I
      1 year ago

      Hi,

       

      In the shared report, I would like to add a Card visual which will display the number of params  per item that have a High or Low value. 

       

      After experimenting a bit, this does not seem to be simple to implement.

       

      Attempt 1

      I have created the below measure and added in a card visual.

      I have then added the measure 'Measure 3 HoL' as a card visual filter and I am not able to set its value. 

       

      _CountOfParams = CALCULATE(DISTINCTCOUNTNOBLANK('Table'[param_id]))

       

      Attempt 2

      I have created the below measures and added then in two card visuals.

      Both card visuals show exactly the same number, 5. This is the total number of params.

      They should instead return respectively 3 and 2.

       

      _CountOfHighParams = CALCULATE(DISTINCTCOUNTNOBLANK('Table'[param_id]), filter('Table','Table'[Measure 3 HoL] = "H"))
       
      _CountOfLowParams = CALCULATE(DISTINCTCOUNTNOBLANK('Table'[param_id]), filter('Table','Table'[Measure 3 HoL] = "L"))
       
      I have also updated the shared pbix file with the abovementioned measures and visuals and I am unfortunately not able to attach it in the post. I am just sharing a screenshot of the report page.
       
      Any suggestions and ideas are more than welcome.
       
      Regards,
      Akis