Powerbi

[Himaja95]

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:

Month	Year	Category	PM	Target Description	Target Threshold - Green	Target Threshold Yellow
January	2022	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.231	0.181
February	2022	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.231	0.181
March	2022	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.231	0.181
April	2022	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.231	0.181
May	2022	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.231	0.181
June	2022	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.231	0.181
July	2022	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.231	0.181
August	2022	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.231	0.181
September	2022	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.231	0.181
October	2022	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.231	0.181
November	2022	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.231	0.181
December	2022	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.231	0.181
January	2023	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.231	0.181
February	2023	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.231	0.181
March	2023	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.231	0.181
April	2023	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.231	0.181
May	2023	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.231	0.181
June	2023	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.231	0.181
July	2023	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.231	0.181
August	2023	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.231	0.181
September	2023	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.231	0.181
October	2023	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.104	0.054
November	2023	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.104	0.054
December	2023	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.104	0.054
January	2024	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.104	0.054

[Ahmedx]

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

[Himaja95]

Thank you

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

[Ahmedx]

share the file and show the target

[Himaja95]

Here is the data

Target is cal as 

For eg : 2023 october target is calculated as 1.104 * Previous year(oct2022)

Year	Month	Cummulative	Target	Target cal
2021	October	1932261
2021	November	3569184
2021	December	5081362
2022	January	6442150
2022	February	7806165
2022	March	9541096
2022	April	11264768
2022	May	12992642
2022	June	14666883
2022	July	16334351
2022	August	18257345
2022	September	20333298
2022	October	2150459	2378613	1.231
2022	November	4016946	4393666	1.231
2022	December	5765406	6255157	1.231
2023	January	7710955	7930287	1.231
2023	February	9528877	9609389	1.231
2023	March	11717531	11745089	1.231
2023	April	13723072	13866929	1.231
2023	May	15755583	15993942	1.231
2023	June	17696324	18054933	1.231
2023	July	19648336	20107586	1.231
2023	August	21872357	22474792	1.231
2023	September	24179580	25030290	1.231
2023	October	2368976	2374107	1.104
2023	November	4472860	4434708	1.104
2023	December	6389242	6365008	1.104

[Ahmedx]

pls try this

[Himaja95]

this worked ,But I have to use the below measure ,which would take the value 1.104 and then I have to calculate the TARGET

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

The actual table is below one fro calcualting the above measure

Month	Year	Category	PM	Target Description	Target Threshold - Green	Target Threshold Yellow
January	2022	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.231	0.181
February	2022	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.231	0.181
March	2022	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.231	0.181
April	2022	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.231	0.181
May	2022	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.231	0.181
June	2022	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.231	0.181
July	2022	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.231	0.181
August	2022	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.231	0.181
September	2022	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.231	0.181
October	2022	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.231	0.181
November	2022	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.231	0.181
December	2022	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.231	0.181
January	2023	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.231	0.181
February	2023	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.231	0.181
March	2023	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.231	0.181
April	2023	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.231	0.181
May	2023	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.231	0.181
June	2023	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.231	0.181
July	2023	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.231	0.181
August	2023	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.231	0.181
September	2023	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.231	0.181
October	2023	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.104	0.054
November	2023	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.104	0.054
December	2023	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.104	0.054
January	2024	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.104	0.05

[Ahmedx · Accepted solution]

pls try this

[Himaja95]

It worked,I really appreciate your help in resolving this issue.👏

[Ahmedx]

you want to get the last year's sum (2150459) and then multiply by 1.104 ?

[Himaja95]

How to calculate the Target for october 2023=

Target = 1.104*2150459(october 2022 cummulative)
I wanted to get the TARGET

[Ahmedx]

why did you write this to the measure?
targets Ridership[PM] = "System Wide Ridership"

[Himaja95]

The column named PM has three metrics in that one of them is System wide ,this refers to this table.

Month	Year	Category	PM	Target Description	Target Threshold - Green	Target Threshold Yellow
January	2022	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.231	0.181
February	2022	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.231	0.181
March	2022	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.231	0.181
April	2022	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.231	0.181
May	2022	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.231	0.181
June	2022	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.231	0.181
July	2022	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.231	0.181
August	2022	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.231	0.181
September	2022	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.231	0.181
October	2022	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.231	0.181
November	2022	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.231	0.181
December	2022	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.231	0.181
January	2023	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.231	0.181
February	2023	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.231	0.181
March	2023	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.231	0.181
April	2023	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.231	0.181
May	2023	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.231	0.181
June	2023	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.231	0.181
July	2023	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.231	0.181
August	2023	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.231	0.181
September	2023	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.231	0.181
October	2023	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.104	0.054
November	2023	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.104	0.054
December	2023	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.104	0.054
January	2024	Ridership	Systemwide Ridership	Increase from last year, with a 5% tolerance	0.104	0.054

[Ahmedx]

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]

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 Modes	Yellow Zone	Green Target
Oct	2024586	2133914	2235143
Nov	3791887	3996649	4186243
Dec	5443501	5737450	6009625
Jan	7284395	7677752	8041972
Feb	9006463	9492812	9943135
Mar	11052814	11649666	12202307
Apr	12935302	13633808	14280573
May	14843310	15644849	16387014
Jun	16667821	17567883	18401274
Jul	18517737	19517695	20443582
Aug	20608348	21721199	22751616
Sept	22784781	24015159	25154398

[Himaja95]

@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 Modes	Yellow Zone	Green Target
Oct	2024586	2133914	2235143
Nov	3791887	3996649	4186243
Dec	5443501	5737450	6009625
Jan	7284395	7677752	8041972
Feb	9006463	9492812	9943135
Mar	11052814	11649666	12202307
Apr	12935302	13633808	14280573
May	14843310	15644849	16387014
Jun	16667821	17567883	18401274
Jul	18517737	19517695	20443582
Aug	20608348	21721199	22751616
Sept	22784781	24015159	25154398

[Idrissshatila]

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