Report Format Help

[GuestUser]

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?

[v-alq-msft]

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]

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

[mwegener]

Hi @GuestUser ,

check this PBIX

[GuestUser]

Thanks @mwegener

But since I am using connect live (ssas tabular) I am not able to use selected value function
Is there any other formula which can help?

[GuestUser]

Hi @mwegener ,

Tried this by replacing it with hasonevalue and somewhat working

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

But when I drag the fiscal year and month on top of TimeType in Column Section (Matrix View) , the report becomes blank.

Any suggestions pls?

[mwegener]

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]

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]

Hi @GuestUser ,

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

[GuestUser]

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]

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]

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 · Accepted solution]

For sure. 

PBIX

[GuestUser]

It's working
Many thanks !!

[v-alq-msft]

Hi, @GuestUser 

You may create two calculated columns in two tables to achieve you requirement.

ActualDes = "Actual"
BudgetDes = "Budget"

However, if you don;t have dates to filter the result, the YTD will calculate the total value and the MTD will calculate the value of max month. YTD and MTD need to change with dates.

Best Regards

Allan