topic Re: Creating Custom Subrows as Percentage Changes in DAX Commands and Tips

Creating Custom Subrows as Percentage Changes

vkisa — Fri, 12 Apr 2024 14:55:02 GMT

Hi,

I need to add a subrow that calculates of percentage change for between specific 2 different time periods as below.

Type	Sum of Revenue
Yesterday	229,925.28
SDLM	196,666.63
Variance1	1.17
Last 7 days	1,348,787.62
7 days SPLM	1,325,468.28
MTD	4,296,514.29
SPLM	4,280,047.93
Variance2	1.00
YTD	13,437,909.68
All	54,389,536.48

I created the date periods before and they are dynamic by slicers, so I'm happy with this format because there are lots of measures except for Revenue.

I have tried to create matrix as below but it shows subtotals, it might give a clue,

You can find the sample report here:

Sample_report

Thank you,

Re: Creating Custom Subrows as Percentage Changes

lbendlin — Fri, 12 Apr 2024 23:28:54 GMT

Here is the general approach

note that you cannot mix data formats unless you convert everything to to text.

Re: Creating Custom Subrows as Percentage Changes

vkisa — Mon, 15 Apr 2024 08:27:42 GMT

Thank you lbendlin , but I need to percentage of change for Variance1 and Variance2 levels rather than 1. Is it possible to define a measure on there?

Re: Creating Custom Subrows as Percentage Changes

lbendlin — Wed, 17 Apr 2024 23:30:50 GMT

yes, that's what you need to do. Replace the "1" with the appropriate measure.

Re: Creating Custom Subrows as Percentage Changes

vkisa — Thu, 18 Apr 2024 07:23:04 GMT

I guess, we can not hide the blank Type_General row, while the subrows appear,right?

Re: Creating Custom Subrows as Percentage Changes

lbendlin — Thu, 18 Apr 2024 14:04:13 GMT

correct

Re: Creating Custom Subrows as Percentage Changes

vkisa — Thu, 18 Apr 2024 14:18:22 GMT

Thank you lbendlin ,

Do you have any idea how can I manage it?

Re: Creating Custom Subrows as Percentage Changes

lbendlin — Thu, 18 Apr 2024 14:29:04 GMT

make the measure return BLANK() for that scenario

Re: Creating Custom Subrows as Percentage Changes

vkisa — Thu, 18 Apr 2024 14:57:03 GMT

Can you give an example for this scenario if it is posssible. I couldn't define variance1 and variance2 as %change of date periods and couldn't display blank on the top row.