Total on 1 column of a dimension

[Roland74]

Hi, i've been struggling a long time with this issue, and AI can't help me.
I'm in direct-query, so i can't do anything in modeling.

I have a facttable with values, and 1 dim-table with project - main-project - projecttype and a lot of other columns.

In the facttable i have values on projects, which i want to totalize on mainproject. But in my table i want to show the project AND the main-project. A small example, this is how i want to show it in my visual but with a lot more columns.

i can't seem to get it right with both the project and the mainproject in the visual.
Any suggestions?

[johnt75]

Try

Amount =
IF (
    SELECTEDVALUE ( 'Table'[proj type] ) = "Main",
    CALCULATE (
        SUM ( 'Table'[Amt] ),
        REMOVEFILTERS ( 'Table' ),
        VALUES ( 'Table'[Main Project] )
    )
)

[Roland74]

Great! This seams to work on the line, but how to get the right total in the Table?
Now its blank.

[johnt75]

You can wrap it inside a SUMX

Amount =
SUMX (
    VALUES ( 'Table'[Project] ),
    IF (
        SELECTEDVALUE ( 'Table'[proj type] ) = "Main",
        CALCULATE (
            SUM ( 'Table'[Amt] ),
            REMOVEFILTERS ( 'Table' ),
            VALUES ( 'Table'[Main Project] )
        )
    )
)

[Roland74]

That doesn't seem to work. I've got a sample .pbix, but cannot upload it.

[Irwan]

hello @Roland74 

Unless your data has workaround before the sum calculation, @johnt75 's solution should work.

you can upload your pbix in dropbox or google drive but remember to give permission to access the pbix.

Thank you.

[Roland74 · Accepted solution]

Hi, Thanx for your reply.
With the sumx i don't get the 'total' row. What i need is:

I Finally got it with this formula. I think it is quite long, is it possible to make this formula shorter?

Amount(with total) = 
    IF(
        HASONEVALUE(DimProject[Project]),
            SUMX (
                VALUES(DimProject[Project]),
                    IF (
                        SELECTEDVALUE(DimProject[Proj.Type]) = "Main",
                            CALCULATE(
                            SUM(FactAmount[Amount]),
                            REMOVEFILTERS(DimProject),
                            VALUES(DimProject[Main Project])
                        )
                    )
                ),
            SUMX(
                SUMMARIZE(
                    DimProject,
                    DimProject[Main Project],
                    "Amount", CALCULATE(SUM(FactAmount[Amount]))
                    ),
                    [Amount]
            )
    )

[v-tejrama]

Hi  @Roland74 ,

Thank you  @Irwan  and @johnt75    for the response provided! 

Has your issue been resolved? If the response provided by the community member addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

Thank you for your understanding!