Forum Discussion

Jessica_17's avatar
Jessica_17
Helper V
2 months ago
Solved

Custom Matrix Visual

Hi Team, I need support in implementing the following requirement in Power BI using DAX. Source Data The data is received from Excel in a monthly format with columns such as: Date BU  WBS ...
Data Protection
Need Help
Tips and Tricks
Tutorial Requests
  • v-dineshya's avatar
    v-dineshya
    2 months ago

    Hi Jessica_17 ,

    Thank you for reaching out to the Microsoft Community Forum. Please refer below output snap and attached .pbix file.

     

     

    Please refer below main measures.

     

    Test Month Value =
    VAR CurrentBU =
        SELECTEDVALUE ( Fact[BU] )
    RETURN
        SWITCH (
            TRUE (),
            CurrentBU IN { "G", "L" },
                [Current Cum Allocation]
                    - COALESCE ( [Previous Cum Allocation], 0 ),
            [GC]
        )
     
    Allocated Amount =
    SWITCH (
        TRUE (),
        ISINSCOPE ( Fact[WBS] )
            && ISINSCOPE ( Fact[Financial Month] ),
            [Test Month Value],
        ISINSCOPE ( Fact[WBS] )
            && NOT ISINSCOPE ( Fact[Financial Month] ),
            SUMX (
                VALUES ( Fact[Financial Month] ),
                CALCULATE ( [Test Month Value] )
            ),
        ISINSCOPE ( Fact[BU] )
            && ISINSCOPE ( Fact[Financial Month] ),
            SUMX (
                VALUES ( Fact[WBS] ),
                CALCULATE ( [Test Month Value] )
            ),
        ISINSCOPE ( Fact[BU] )
            && NOT ISINSCOPE ( Fact[Financial Month] ),
            SUMX (
                VALUES ( Fact[WBS] ),
                CALCULATE (
                    SUMX (
                        VALUES ( Fact[Financial Month] ),
                        CALCULATE ( [Test Month Value] )
                    )
                )
            ),
        SUMX (
            VALUES ( Fact[BU] ),
            CALCULATE (
                SUMX (
                    VALUES ( Fact[WBS] ),
                    CALCULATE (
                        SUMX (
                            VALUES ( Fact[Financial Month] ),
                            CALCULATE ( [Test Month Value] )
                        )
                    )
                )
            )
        )
    )
     

    I hope this information helps. Please do let us know if you have any further queries.

     

    Regards,

    Dinesh

     

     

v-dineshya's avatar
v-dineshya
Community Support
2 months ago

Hi Jessica_17 ,

Thank you for reaching out to the Microsoft Community Forum. Please refer below output snap and attached .pbix file.

 

 

Please refer below main measures.

 

Test Month Value =
VAR CurrentBU =
    SELECTEDVALUE ( Fact[BU] )
RETURN
    SWITCH (
        TRUE (),
        CurrentBU IN { "G", "L" },
            [Current Cum Allocation]
                - COALESCE ( [Previous Cum Allocation], 0 ),
        [GC]
    )
 
Allocated Amount =
SWITCH (
    TRUE (),
    ISINSCOPE ( Fact[WBS] )
        && ISINSCOPE ( Fact[Financial Month] ),
        [Test Month Value],
    ISINSCOPE ( Fact[WBS] )
        && NOT ISINSCOPE ( Fact[Financial Month] ),
        SUMX (
            VALUES ( Fact[Financial Month] ),
            CALCULATE ( [Test Month Value] )
        ),
    ISINSCOPE ( Fact[BU] )
        && ISINSCOPE ( Fact[Financial Month] ),
        SUMX (
            VALUES ( Fact[WBS] ),
            CALCULATE ( [Test Month Value] )
        ),
    ISINSCOPE ( Fact[BU] )
        && NOT ISINSCOPE ( Fact[Financial Month] ),
        SUMX (
            VALUES ( Fact[WBS] ),
            CALCULATE (
                SUMX (
                    VALUES ( Fact[Financial Month] ),
                    CALCULATE ( [Test Month Value] )
                )
            )
        ),
    SUMX (
        VALUES ( Fact[BU] ),
        CALCULATE (
            SUMX (
                VALUES ( Fact[WBS] ),
                CALCULATE (
                    SUMX (
                        VALUES ( Fact[Financial Month] ),
                        CALCULATE ( [Test Month Value] )
                    )
                )
            )
        )
    )
)
 

I hope this information helps. Please do let us know if you have any further queries.

 

Regards,

Dinesh