I have the following data
IF OBJECT_ID('tempdb..#TBL') IS NOT NULL DROP TABLE #TBL
create table #TBL (category varchar(6), Year_Vs_Year VARCHAR(20), Cost INT)
INSERT INTO #TBL VALUES
(null,'24/25 vs 19/20',7),(null,'24/25 vs 20/21',208),(null,'24/25 vs 21/22',98),
(null,'24/25 vs 22/23',28),(null,'24/25 vs 23/24',67),(null,'24/25 vs 24/25',28),
('AE','24/25 vs 19/20',881959),('AE','24/25 vs 20/21',1424903),('AE','24/25 vs 21/22',3052440),
('AE','24/25 vs 22/23',1975536),('AE','24/25 vs 23/24',1524131),('AE','24/25 vs 24/25',956217),
('DP','24/25 vs 19/20',133),('DP','24/25 vs 20/21',6),('DP','24/25 vs 21/22',20),
('DP','24/25 vs 22/23',6),('DP','24/25 vs 23/24',17),('DY','24/25 vs 19/20',66587),
('DY','24/25 vs 20/21',53731),('DY','24/25 vs 21/22',55157),('DY','24/25 vs 22/23',68488),
('DY','24/25 vs 23/24',75492),('DY','24/25 vs 24/25',46234),('EV','24/25 vs 19/20',93),
('EV','24/25 vs 20/21',50),('EV','24/25 vs 21/22',342),('EV','24/25 vs 23/24',714),('EV','24/25 vs 24/25',64),
('GP','24/25 vs 19/20',3513842),('GP','24/25 vs 20/21',2499217),('GP','24/25 vs 21/22',3266166),
('GP','24/25 vs 22/23',3517609),('GP','24/25 vs 23/24',4133396),('GP','24/25 vs 24/25',2804384),
('IP','24/25 vs 19/20',2258417),('IP','24/25 vs 20/21',2622805),
('IP','24/25 vs 21/22',3613080),('IP','24/25 vs 22/23',2905209),('IP','24/25 vs 23/24',2632569),('IP','24/25 vs 24/25',1768455),
('OH','24/25 vs 19/20',42040),('OH','24/25 vs 20/21',73167),('OH','24/25 vs 21/22',81570),('OH','24/25 vs 22/23',27576),
('OH','24/25 vs 23/24',23651),('OH','24/25 vs 24/25',14776),('OP','24/25 vs 19/20',2185227),('OP','24/25 vs 20/21',1973621),
('OP','24/25 vs 21/22',3078435),('OP','24/25 vs 22/23',2795034),('OP','24/25 vs 23/24',2735693),('OP','24/25 vs 24/25',1791504),
('PP','24/25 vs 19/20',74566),('PP','24/25 vs 20/21',27627),('PP','24/25 vs 21/22',22976),('PP','24/25 vs 22/23',12624),
('PP','24/25 vs 23/24',11224),('PP','24/25 vs 24/25',2928),('QC','24/25 vs 19/20',42588),('QC','24/25 vs 20/21',44024),
('QC','24/25 vs 21/22',64487),('QC','24/25 vs 22/23',69394),('QC','24/25 vs 23/24',84978),('QC','24/25 vs 24/25',48377),
('RE','24/25 vs 19/20',32),('RE','24/25 vs 20/21',44),('UN','24/25 vs 19/20',46788),('UN','24/25 vs 20/21',302300),
('UN','24/25 vs 21/22',368105),('UN','24/25 vs 22/23',116676),('UN','24/25 vs 23/24',58059),('UN','24/25 vs 24/25',42589)
select * from #TBL
Currently the data is like this
in power bi i wrote the code bellow
Cost without current year =
var a = MAX(Pathology[fiscal_year])
var b =CALCULATE(([Cost]),a= Pathology[fiscal_year])
var c = CALCULATE(SUM(Pathology[Cost]),ALL('Pathology'))
RETURN
c
Expected output should be the value of the max year
All the values should max year value as displayed above Thanks
This Dax measure produce the expected result:
Max Cost without Year =
VAR __maxYear = CALCULATE( MAX( Pathology[fiscal_year]), ALL(Pathology ) )
VAR __result = CALCULATE( MAX( Pathology[Cost] ), Pathology[fiscal_year] = __maxYear )
VAR __subtable =
FILTER(
ALL( Pathology ),
Pathology[fiscal_year] = __maxYear
)
VAR __sum =
SUMMARIZE(
__subtable,
Pathology[Category],
"@MaxCost", SUM( Pathology[Cost] )
)
RETURN
IF(
HASONEVALUE( Pathology[Category]),
__result,
SUMX( __sum, [@MaxCost] )
)