Orderbook value calculated in hierarchy

[JohanData]

Hi folks,

I'm struggling with how to calculate the 2 orderbook versions in Power BI. Thanks in advance. See screenshot below. 

[v-juanli-msft · Accepted solution]

Hi @JohanData 

Test with this table

cate	mainprojectnr	projectnr	budjet	actual
parent	123	123	0	0
child1	123	123.001	60	120
child2	123	123.002	30	10

Create measure

Measure_maxvalue = MAX(SUM(Table1[budjet])-SUM(Table1[actual]),0)


version1 =
IF (
    HASONEVALUE ( Table1[cate] ),
    BLANK (),
    MAX (
        CALCULATE ( SUM ( Table1[budjet] ), ALL ( Table1 ) )
            - CALCULATE ( SUM ( Table1[actual] ), ALL ( Table1 ) ),
        0
    )
)


version2 = IF(HASONEVALUE(Table1[cate]),[Measure_maxvalue],SUMX(ALL(Table1),[Measure_maxvalue]))

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-juanli-msft · Accepted solution]

Hi @JohanData 

Test with this table

cate	mainprojectnr	projectnr	budjet	actual
parent	123	123	0	0
child1	123	123.001	60	120
child2	123	123.002	30	10

Create measure

Measure_maxvalue = MAX(SUM(Table1[budjet])-SUM(Table1[actual]),0)


version1 =
IF (
    HASONEVALUE ( Table1[cate] ),
    BLANK (),
    MAX (
        CALCULATE ( SUM ( Table1[budjet] ), ALL ( Table1 ) )
            - CALCULATE ( SUM ( Table1[actual] ), ALL ( Table1 ) ),
        0
    )
)


version2 = IF(HASONEVALUE(Table1[cate]),[Measure_maxvalue],SUMX(ALL(Table1),[Measure_maxvalue]))

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JohanData · Accepted solution]

thanks 🙂