powerbipowerquerypowerbi-desktoppowerbi-embeddedpower-bi-report-server

Power BI make Measure static Value and dynamic


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

enter image description here

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

It is given funny result enter image description here

Expected output should be the value of the max year enter image description here

All the values should max year value as displayed above Thanks

enter image description here


Solution

  • 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] ) 
        )
    

    enter image description here