Design issue

[PBIDEV_10]

Hello All

I have few measures for New subscriptions and Total subscriptions. when I put it in the table I get output like 

New subscriptions YTD	New subscriptions Same period last yr	New subscriptions Varience%	Total subscriptions YTD	Total subscriptions Same period last yr	Total subscriptions varience %

But actually I want to format table to show like this:

	YTD	Same period last yr	Varience %
New subscriptions
Total subscriptions

How do I do that? 

BR,

PP

[v-zhangti · Accepted solution]

Hi, @PBIDEV_10 

You can refer to my method to see if you can achieve the results you expect.

Check Transpose in the power query

Use First Row as Headers

Add Index Column

Add calculated columns in Desktop, and rename 2020 and 2021.

Column = IF([Index]=1,"New subscriptions","Total subscriptions")

Varience % = DIVIDE([YTD]-[Same period last yr],[YTD])

Best Regards,

Community Support Team _Charlotte

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

[PaulDBrown · Accepted solution]

Here is one way.

1) create an independent table using the enter data in the ribbon and type in the values you need for the rows in the matrix and an index column. In my example (I.ve named the table "Matrix Layout"):

2) I'm comparing sales for A vs total sales, so I'm using these base measures (adjust according to what you need:

Sum Sales = SUM(FactTable[Forecast])

Sales for A = CALCULATE([Sum Sales],'Dim Channel'[Channel] = "A")

3) for the matrix, create the measures for each of the columns using the equivalent to the following logic( the relevant code is the SWITCH function; the detail is up to what you need):

YTD =
SWITCH (
    SELECTEDVALUE ( 'Matrix layout'[Index] ),
    1, [Sales for A],
    [Sum Sales]
)

Last Year =
SWITCH (
    SELECTEDVALUE ( 'Matrix layout'[Index] ),
    1, CALCULATE ( [Sales for A], PREVIOUSYEAR ( 'Date Table'[Date] ) ),
    CALCULATE ( [Sum Sales], PREVIOUSYEAR ( 'Date Table'[Date] ) )
)

Variance =
VAR PYA =
    CALCULATE ( [Sales for A], PREVIOUSYEAR ( 'Date Table'[Date] ) )
VAR PYS =
    CALCULATE ( [Sum Sales], PREVIOUSYEAR ( 'Date Table'[Date] ) )
RETURN
    SWITCH (
        SELECTEDVALUE ( 'Matrix layout'[Index] ),
        1, [Sales for A] - PYA,
        [Sum Sales] - PYS
    )

4) Create the matrix using the matrix layout column as rows and add the measures as values to get:

[v-zhangti · Accepted solution]

Hi, @PBIDEV_10 

You can refer to my method to see if you can achieve the results you expect.

Check Transpose in the power query

Use First Row as Headers

Add Index Column

Add calculated columns in Desktop, and rename 2020 and 2021.

Column = IF([Index]=1,"New subscriptions","Total subscriptions")

Varience % = DIVIDE([YTD]-[Same period last yr],[YTD])

Best Regards,

Community Support Team _Charlotte

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

[Syk]

How do you determine a new sub?
I'd suggest having 2 base measures "New subscriptions" and "Total Subscriptions"
Then you can find the YTD/variance/whatever else from these and use them in a matrix for example

[PBIDEV_10]

@Syk Yes I have two base measures as new subscriptions and Total subscriptions. And calculating Ytd etc is not an issue. The problem is when I am using matrix visual, as per its def, it is not possible to add a measure in row or column. So how I can produce the desired output?

[Syk]

Try putting your date field (sounds like you want year) in the row section and your measures in the value

[PBIDEV_10]

Yes. But to calculate YTD I have to use the formula DATESYTD(Calendar[Date]) which is nothing but a measure. 

[Syk]

Dont use the measure but the actual date field

[PBIDEV_10]

But I dont want to show date in rows. May I ask if you can put your suggestions in a pbix file if possible? Since I am not able to understand fully how the desired output can be achieved with your suggestions.

[Syk]

Something similar to this..

[PBIDEV_10]

@Syk But then New and Total subscription measures will be like one after another and not one below another.

[Syk]

Use the matrix visual instead of the table. Put your subscribtions in the row spot and YTD, variance in the column spot

[PBIDEV_10]

@Syk Itried to use matrix but it didnt help. Can you explain bit more about it?