Forum Discussion

Jessica_17's avatar
Jessica_17
Icon for Helper V rankHelper V
4 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
    4 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

     

     

Kedar_Pande's avatar
Kedar_Pande
Icon for Super User rankSuper User
4 months ago

  

You need two measures:

Cumulative Allocated = 
VAR _ratio = SELECTEDVALUE('Data'[G_Ratio]) 
VAR _cumTotal = 
CALCULATE(
SUM('Data'[Group currency]),
FILTER(
ALL('Data'[financial month]),
'Data'[financial month] <= MAX('Data'[financial month])
)
)
RETURN _cumTotal * _ratio
Monthly Value = 
VAR _current = [Cumulative Allocated]
VAR _prior = 
CALCULATE(
[Cumulative Allocated],
FILTER(
ALL('Data'[financial month]),
'Data'[financial month] = MAX('Data'[financial month]) - 1
)
)
RETURN _current - COALESCE(_prior, 0)


The ratio differs by BU, so drive _ratio with the row's BU using G_Ratio for G and L_Ratio for L. Put financial month on columns and BU on rows in the matrix.

If this answer helped, please click 👍 or Accept as Solution.
-Kedar
LinkedIn: https://www.linkedin.com/in/kedar-pande

Jessica_17

  • Jessica_17's avatar
    Jessica_17
    Icon for Helper V rankHelper V
    4 months ago

    Hello  ,

    This is not providing the correct output as required.
    Here is my sample data

    DateBUWBSGroup CurrencyFinancial MonthG_RatioL_RatioSUB_GROUP
    04-04-2026G234PGM3510.2360.764A
    04-04-2026G23423PLL34510.2360.764A
    04-04-2026L234PGM23410.2360.764A
    04-04-2026L23423PLL234210.2360.764A
    10-04-2026G76876FG423410.2360.764A
    10-04-2026G89789GHN53410.2360.764A
    10-04-2026L76876FG64510.2360.764A
    10-04-2026L89789GHN6410.2360.764A
    14-04-2026G868GHHB5610.2360.764B
    14-04-2026G8798JKK4510.2360.764B
    14-04-2026L868GHHB23410.2360.764B
    14-04-2026L8798JKK3210.2360.764B
    03-05-2026G3466HGJ56820.38590.6141B
    03-05-2026G8907HGY46520.38590.6141B
    03-05-2026L3466HGJ4620.38590.6141B
    03-05-2026L8907HGY45645620.38590.6141B
    15-05-2026G56765OLK34534520.38590.6141C
    15-05-2026G54587DGB23420.38590.6141C
    15-05-2026L56765OLK32420.38590.6141C
    15-05-2026L54587DGB3220.38590.6141C
    24-05-2026G6797UGM76520.38590.6141C
    24-05-2026G78656HSC4620.38590.6141C
    24-05-2026L6797UGM23420.38590.6141C
    24-05-2026L78656HSC23520.38590.6141C
    04-05-2026G234PGM2320.38590.6141A
    04-05-2026G23423PLL56720.38590.6141A
    04-05-2026L234PGM23420.38590.6141A
    04-05-2026L23423PLL67820.38590.6141A
    10-05-2026G76876FG56720.38590.6141A
    10-05-2026G89789GHN4520.38590.6141A
    10-05-2026L76876FG67820.38590.6141A
    10-05-2026L89789GHN4320.38590.6141A
    14-05-2026G868GHHB626220.38590.6141B
    14-05-2026G8798JKK95920.38590.6141B
    14-05-2026L868GHHB23420.38590.6141B
    14-05-2026L8798JKK59820.38590.6141B
    03-04-2026G3466HGJ65210.2360.764B
    03-04-2026G8907HGY59810.2360.764B
    03-04-2026L3466HGJ598410.2360.764B
    03-04-2026L8907HGY23210.2360.764B
    15-04-2026G56765OLK29510.2360.764C
    15-04-2026G54587DGB59810.2360.764C
    15-04-2026L56765OLK5210.2360.764C
    15-04-2026L54587DGB98910.2360.764C
    24-04-2026G6797UGM986510.2360.764C
    24-04-2026G78656HSC95910.2360.764C
    24-04-2026L6797UGM5610.2360.764C
    24-04-2026L78656HSC656910.2360.764C


    and here is my expected output for validation on BU level

     

     

    for g it should work with g_ratio, for L it should work with l_ratio, and for rest where BU are not g or l, simply sum(group currency), also it should work with sum of g&L, not separately.

    I have updated the excel as well with formula, you can refer that as well.

    • v-dineshya's avatar
      v-dineshya
      Icon for Community Support rankCommunity Support
      4 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

       

       

      • Jessica_17's avatar
        Jessica_17
        Icon for Helper V rankHelper V
        4 months ago

        Thanks v-dineshya ,

        It worked for me.

    • Jessica_17's avatar
      Jessica_17
      Icon for Helper V rankHelper V
      4 months ago

      I have updated the excel data as well little bit, its little different from what I pasted in above message.