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            ...
Need Help
  • mwegener's avatar
    mwegener
    6 years ago

    For sure. 

    PBIX

     

mwegener's avatar
mwegener
Most Valuable Professional
6 years ago

Hi GuestUser ,

 

try this.

 

Measure =
IF (
    HASONEVALUE ( MeasureType[MeasureType] ) && HASONEVALUE ( TimeType[TimeType] ),
    SWITCH (
        VALUES ( MeasureType[MeasureType] ),
        "Actual", SWITCH (
            VALUES ( TimeType[TimeType] ),
            "MTD", [Actual Count-MTD],
            "YTD", [Actual Count-YTD],
            BLANK ()
        ),
        "Budget", SWITCH (
            VALUES ( TimeType[TimeType] ),
            "MTD", [Budget Count-MTD],
            "YTD", [Budget Count-YTD],
            BLANK ()
        ),
        BLANK ()
    ),
    BLANK ()
)

 

GuestUser's avatar
GuestUser
Helper V
6 years ago

Hi mwegener 

 

Thanks!!

 

But when I drag the column fiscal year and fiscal month from Date Dimension on top of Time Type Column, the report becomes blank

 

Since i need the report in below format

 

                  2019-Jan                        2019-Feb

                MTD    YTD                    MTD      YTD

Actual

Budget

Why is it so? Any suggestions pls

  • mwegener's avatar
    mwegener
    Most Valuable Professional
    6 years ago

    Hi GuestUser ,

     

    did you do an "expand all down one level in the hierarchy"?

     

     

     

     

     

  • GuestUser's avatar
    GuestUser
    Helper V
    6 years ago
    Hi mwegener

    Yes..I did....the report does not show any data
    If I drag fiscal month columnn from date dimension on top of time type column...the report does not show data

    But if I drag fiscal month column below timetype in column section of matrix view..I am able to see data

    Any help on this pls...since i need to achieve the same format of the report stated above
  • mwegener's avatar
    mwegener
    Most Valuable Professional
    6 years ago

    Hi GuestUser ,

     

    it doesn't sound like you're "Expand all down one level in the hierachy"

    If the measure is not filtered by TimeType, it returns Blank().

     

  • GuestUser's avatar
    GuestUser
    Helper V
    6 years ago
    Hi mwegener

    Ya..I m getting the first screenshot which u have posted..
    Though I clicked on drill down icon as well...it still shows blank...will check once more..may be i m going wrong somewhere..

    Also if u can share the pbix file pls..(second screenshot in which the correct output is coming )
  • mwegener's avatar
    mwegener
    Most Valuable Professional
    6 years ago

    For sure. 

    PBIX

     

  • GuestUser's avatar
    GuestUser
    Helper V
    6 years ago
    It's working
    Many thanks !!