Forum Discussion

GuestUser's avatar
GuestUser
Helper V
6 years ago
Solved

Report Format Help

Hi ,

 

I have 4 measures

 

Actual Count-MTD -- From one fact table
Actual Count-YTD
Budget Count _MTD -- From other fact table
Budget Count-YTD

 

Need the report in below format

                                                     2019
                         Actual Count-MTD     Budget Count - YTD
Actual 
Budgeted

 

where Actual Count MTD should show values for Actual Count-MTD and Budget Count _MTD in one column only
and Budget Count - YTD should show values for Actual Count-YTD and Budget Count-YTD in one column only

 

and "Actual" and "Budgeted" should be hardcoded values which is present at Left hand Side

 

and 2019 comes from Fiscal Year column of Date Table

 

Not able to achieve the report format as shown above

 

Any ideas please?

Need Help

14 Replies

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    6 years ago

    Hi, GuestUser 

     

    Based on your description, I created data to reproduce your scenario.

    Actual:

    Budget:

    DateTable(a calculated table): 

     

    DateTable = CALENDARAUTO()

     

     

    You may create measures as follows.

     

    Actual Count-MTD = 
TOTALMTD(
    SUM(Actual[Count]),
    DateTable[Date]
) 
Actual Count-YTD = 
TOTALYTD(
    SUM(Actual[Count]),
    DateTable[Date]
)
Budget Count-MTD = 
TOTALMTD(
    SUM(Budget[Value]),
    DateTable[Date]
)
Budget Count-YTD = 
TOTALYTD(
    SUM(Budget[Value]),
    DateTable[Date]
)
MTD = 
IF(
        ISINSCOPE(Budget[Budget Date]),
        Budget[Budget Count-MTD],
        Actual[Actual Count-MTD]
)
YTD = 
IF(
        ISINSCOPE(Budget[Budget Date]),
        Budget[Budget Count-YTD],
        Actual[Actual Count-YTD]
)

     

     

    Then you can create a matrix visual as below. The first level(the blue one) on the row is Actual Date and the second level(the black one) is Budget Date.

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

    • GuestUser's avatar
      GuestUser
      Helper V
      6 years ago

      Thanks v-alq-msft for your reply

       

      But in the report format, on left hand side - i do not need the dates , I need a hardcoded Value (which does not come from any table) -- "Actual" and "Budget"

       

      like

                       2019

                       MTD       YTD

      Actual

      Budget

       

      Can you pls suggest