cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Fcoatis Member
Member

% Change in a non contiguous Matrix

I have this matrix with measure Tot Gross = sum(INVEST[Gross Bal]) . How do I calculate the percentual difference of Tot Gross (being calendar non contiguous due to holidays and weekends)?

 

Matrix.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
v-huizhn-msft Super Contributor
Super Contributor

Re: % Change in a non contiguous Matrix

Hi @Fcoatis,

You can use a measure, please review the following solution.

I have sample data table.

1.PNG

Add index column in Edit Query-> Close & Apply. You will get the following table.

2.PNG

Create a calculated column using the formula.

Last = LOOKUPVALUE(Table9[value],Table9[Index.1],Table9[Index.1]-1)


3.png

Finally, create a measure. For unique date, sum(Table9[value]) equals itself. Create a table visual used to display the result.

Percentage1 = (SUM(Table9[value])-SUM(Table9[Last]))/SUM(Table9[Last])


Capture2.PNG


If you have other issues, please let me know.

Best Regards,
Angelia



4 REPLIES 4
v-huizhn-msft Super Contributor
Super Contributor

Re: % Change in a non contiguous Matrix

Hi @Fcoatis,

What's the mean of the percentual difference of Tot Gross, could you please post an example? 

The difference includes increase and decrease between the two numbers you are comparing.

Increase = (New Number - Original Number)÷ Original Number × 100.

Decrease = (Original Number - New Number)÷ Original Number × 100.

In your requirement, what's the New Number and Original Number?

Best Regards,
Angelia

Fcoatis Member
Member

Re: % Change in a non contiguous Matrix

Hi Angelina,

 

I'm looking for a measure that calculates (actual value/previous value)-1

 

PerDiff.PNG

v-huizhn-msft Super Contributor
Super Contributor

Re: % Change in a non contiguous Matrix

Hi @Fcoatis,

You can use a measure, please review the following solution.

I have sample data table.

1.PNG

Add index column in Edit Query-> Close & Apply. You will get the following table.

2.PNG

Create a calculated column using the formula.

Last = LOOKUPVALUE(Table9[value],Table9[Index.1],Table9[Index.1]-1)


3.png

Finally, create a measure. For unique date, sum(Table9[value]) equals itself. Create a table visual used to display the result.

Percentage1 = (SUM(Table9[value])-SUM(Table9[Last]))/SUM(Table9[Last])


Capture2.PNG


If you have other issues, please let me know.

Best Regards,
Angelia



Fcoatis Member
Member

Re: % Change in a non contiguous Matrix

Thank you Angelia,

 

I think this is the solution. I'll have to adapt to my model in order to have the right sequential index.

 

Best regards

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 6 members 2,813 guests
Please welcome our newest community members: