Forum Discussion

Himaja95's avatar
Himaja95
Helper I
2 years ago
Solved

Powerbi

Hi

 

I am getting this error,when i am trying calculate the targets

error:

Calculation error in measure targets_Ridership(Bus _Ridership_Green):A table of multiple values were supplied ,where a single value was expected

 

Code

SystemWide_Green = CALCULATE(VALUES(targets_Ridership[Target Threshold - Green]),FILTER(targets_Ridership, targets_Ridership[PM] = "Systemwide Ridership"))

 

This is the data:

MonthYearCategoryPMTarget DescriptionTarget Threshold - GreenTarget Threshold Yellow
January2022RidershipSystemwide RidershipIncrease from last year, with a 5% tolerance0.2310.181
February2022RidershipSystemwide RidershipIncrease from last year, with a 5% tolerance0.2310.181
March2022RidershipSystemwide RidershipIncrease from last year, with a 5% tolerance0.2310.181
April2022RidershipSystemwide RidershipIncrease from last year, with a 5% tolerance0.2310.181
May2022RidershipSystemwide RidershipIncrease from last year, with a 5% tolerance0.2310.181
June2022RidershipSystemwide RidershipIncrease from last year, with a 5% tolerance0.2310.181
July2022RidershipSystemwide RidershipIncrease from last year, with a 5% tolerance0.2310.181
August2022RidershipSystemwide RidershipIncrease from last year, with a 5% tolerance0.2310.181
September2022RidershipSystemwide RidershipIncrease from last year, with a 5% tolerance0.2310.181
October2022RidershipSystemwide RidershipIncrease from last year, with a 5% tolerance0.2310.181
November2022RidershipSystemwide RidershipIncrease from last year, with a 5% tolerance0.2310.181
December2022RidershipSystemwide RidershipIncrease from last year, with a 5% tolerance0.2310.181
January2023RidershipSystemwide RidershipIncrease from last year, with a 5% tolerance0.2310.181
February2023RidershipSystemwide RidershipIncrease from last year, with a 5% tolerance0.2310.181
March2023RidershipSystemwide RidershipIncrease from last year, with a 5% tolerance0.2310.181
April2023RidershipSystemwide RidershipIncrease from last year, with a 5% tolerance0.2310.181
May2023RidershipSystemwide RidershipIncrease from last year, with a 5% tolerance0.2310.181
June2023RidershipSystemwide RidershipIncrease from last year, with a 5% tolerance0.2310.181
July2023RidershipSystemwide RidershipIncrease from last year, with a 5% tolerance0.2310.181
August2023RidershipSystemwide RidershipIncrease from last year, with a 5% tolerance0.2310.181
September2023RidershipSystemwide RidershipIncrease from last year, with a 5% tolerance0.2310.181
October2023RidershipSystemwide RidershipIncrease from last year, with a 5% tolerance0.1040.054
November2023RidershipSystemwide RidershipIncrease from last year, with a 5% tolerance0.1040.054
December2023RidershipSystemwide RidershipIncrease from last year, with a 5% tolerance0.1040.054
January2024RidershipSystemwide RidershipIncrease from last year, with a 5% tolerance0.1040.054
Need Help

16 Replies

  • Idrissshatila's avatar
    Idrissshatila
    Super User
    2 years ago

    Hello Himaja95 ,

     

    but what are you trying to calculate, if you're trying to get the sum of this column then try the following measure:

     

    SystemWide_Green = CALCULATE(SUM(targets_Ridership[Target Threshold - Green]),FILTER(targets_Ridership, targets_Ridership[PM] = "Systemwide Ridership"))

     

  • Himaja95's avatar
    Himaja95
    Helper I
    2 years ago

    Idrissshatila  

    The code worked but its not showing the right numbers in the chart.

    Yes I am trying to display the targets. and  little calculation behind displaying the targets

    Systems_Ridership_Gauge = ([SystemWide_Green]+1) * [Ridership Total Last Year All Modes]
    Ridership total last year is the (FY 2023 Actual ) column.  in the below table
    By the above code you sent me its is showing 5.4M instead of 4.1

     

     Ridership Total Last Year All ModesYellow ZoneGreen Target
    Oct202458621339142235143
    Nov379188739966494186243
    Dec544350157374506009625
    Jan728439576777528041972
    Feb900646394928129943135
    Mar110528141164966612202307
    Apr129353021363380814280573
    May148433101564484916387014
    Jun166678211756788318401274
    Jul185177371951769520443582
    Aug206083482172119922751616
    Sept227847812401515925154398

     

  • Himaja95's avatar
    Himaja95
    Helper I
    2 years ago

    Thank you for your response Fowmy 

    The code worked but its not showing the right numbers in the chart.

    Yes I am trying to display the targets. and  little calculation behind displaying the targets

    Systems_Ridership_Gauge = ([SystemWide_Green]+1) * [Ridership Total Last Year All Modes]
    Ridership total last year is the (FY 2023 Actual ) column.  in the below table
    By the above code you sent me its is showing 5.4M instead of 4.1

     

     Ridership Total Last Year All ModesYellow ZoneGreen Target
    Oct202458621339142235143
    Nov379188739966494186243
    Dec544350157374506009625
    Jan728439576777528041972
    Feb900646394928129943135
    Mar110528141164966612202307
    Apr129353021363380814280573
    May148433101564484916387014
    Jun166678211756788318401274
    Jul185177371951769520443582
    Aug206083482172119922751616
    Sept227847812401515925154398

     

  • Ahmedx's avatar
    Ahmedx
    Super User
    2 years ago

    pls try this

     

     

    SystemWide_Green =
 CALCULATE(MAX(targets_Ridership[Target Threshold - Green]),
    KEEPFILTERS(FILTER(ALL(targets_Ridership), 
      targets_Ridership[PM] = "Systemwide Ridership")))
-------or-----------------------
SystemWide_Green =
 CALCULATE(MAX(targets_Ridership[Target Threshold - Green]),
        FILTER(ALL(targets_Ridership), 
              targets_Ridership[PM] = "Systemwide Ridership"))

     

     

    • Ahmedx's avatar
      Ahmedx
      Super User
      2 years ago

      and try this

      SystemWide_Green =
 CALCULATE(SUM(targets_Ridership[Target Threshold - Green]),
    KEEPFILTERS(FILTER(ALL(targets_Ridership), 
      targets_Ridership[PM] = "Systemwide Ridership")))
-------or-----------------------
SystemWide_Green =
 CALCULATE(SUM(targets_Ridership[Target Threshold - Green]),
        FILTER(ALL(targets_Ridership), 
              targets_Ridership[PM] = "Systemwide Ridership"))
    • Himaja95's avatar
      Himaja95
      Helper I
      2 years ago

      Thank you

      Unfortunately didn't work for me,the code doesnt give any errors but its not displaying the right numbers