cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
imar Frequent Visitor
Frequent Visitor

Combine measures in Matrix visual and calculate variance by date and period

Dear datanauts,

 

I am trying to combine measures in a matrix split by date and period (Actual and Budget).

You can access my files here (please take a copy locally):

https://www.dropbox.com/sh/x2rzap374vven36/AACA94W9ZdKosUEbleaoZ-4ja?dl=0

 

 

Excel Report.jpg

is what I am trying to reproduce from Excel to Power BI

 

Excel Report.JPG

 

 

example.xlsx

is my data source excel file

 

example.pbix

is my power bi model

 

example.xlsx contents

Sheet "Data"

One table named Financials which contains Revenues and Expenses split by month and period.

Another table named Operationals which contains number of visitors split by month and period.

 

Sheet "Calendar"

A table with reported Dates

 

Sheet "Business Unit"

A table with Business Units

 

Sheet "Period"

A table with a split of periods I need to report (Actual, Budget and Last Year)

 

Sheet "Report"

The report I am trying to reproduce from Excel to Power BI

 

I have created the following model in Power BI

 

model.JPG

 

 

Measures Approach 1

I have created the following measures

 

Revenues = calculate(sum(Financials[Value]),Financials[Category]="Revenues")

Visitors = sum(Operationals[Value])

Revenues per Visitor = iferror([Revenues]/[Visitors];0)

 

 

 

This approach (Measures approach 1) is not working as I can't display Last Year and their variance from Actuals in Table 1 matrix visualTable 1.JPG

 

Measures Approach 2

I have created the following measures:

Revenues by Period =

switch(

 true(),

 values(Period[Period])="Actual",[Revenues Actual],

 values(Period[Period])="Budget",[Revenues Budget],

 values(Period[Period])="LY",[Revenues LY])

 

Visitors by Period =

switch(

 true(),

 values(Period[Period])="Actual",[Visitors Actual],

 values(Period[Period])="Budget",[Visitors Budget],

 values(Period[Period])="Actual",[Visitors LY])

 

Revenues per Visitor by Period = [Revenues by Period]/[Visitors by Period]

 

 

 

This approach (Measures approach 2) is not working as Last Year is not displayed although included in the switch (Table 2 matrix visual).Table 2.JPG

My hint is that "Actual" is mentioned twice and switch is reading the first line.

Additionally, I think I am not able to create in columns the variances from Actuals.

 

Approach 3

I have created the follwing measures under Financials table:

Revenues Actual = calculate(sum(Financials[Value]),Financials[Category]="Revenues",Financials[Period]="Actual")

Revenues Budget = calculate(sum(Financials[Value]),Financials[Category]="Revenues",Financials[Period]="Budget")

Revenues LY = calculate([Revenues Actual],SAMEPERIODLASTYEAR('Calendar'[Reported Date]))

Revenues Δ Act vs Bgt = [Revenues Actual]-[Revenues Budget]

Revenues % Act vs Bgt = iferror([Revenues Δ Act vs Bgt]/[Revenues Budget];0)

Revenues Δ Act vs LY = [Revenues Actual]-[Revenues LY]

Revenues % Act vs LY = iferror([Revenues Δ Act vs LY]/[Revenues LY];0)

 

 

 

Again this approach is not working (Table 3 matrix visual) as I would like measures to be listed vertically.Table 3.JPG

 

Any help would be much appreciated.

Once again my target is to reproduce in Power BI the report displayed at Excel Report.jpg

 

In case you come up with a solution feel free to upload your files in the same link with different file names.

 

Thank you for your time!!!

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Icey New Contributor
New Contributor

Re: Combine measures in Matrix visual and calculate variance by date and period

Hi @imar ,

 

You can create your measures like so:

Actual 2 =
VAR t =
    UNION ( Financials, Operationals )
RETURN
    IF (
        HASONEVALUE ( Category[Category] ),
        SUMX ( FILTER ( t, [Period] = "Actual" ), [Value] ),
        DIVIDE (
            SUMX ( FILTER ( t, [Period] = "Actual" && [Category] = "Revenues" ), [Value] ),
            SUMX ( FILTER ( t, [Period] = "Actual" && [Category] = "Visitors" ), [Value] )
        )
    )
Budget 2 =
VAR t =
    UNION ( Financials, Operationals )
RETURN
    IF (
        HASONEVALUE ( Category[Category] ),
        SUMX ( FILTER ( t, [Period] = "Budget" ), [Value] ),
        DIVIDE (
            SUMX ( FILTER ( t, [Period] = "Budget" && [Category] = "Revenues" ), [Value] ),
            SUMX ( FILTER ( t, [Period] = "Budget" && [Category] = "Visitors" ), [Value] )
        )
    )
Δ Act vs Bgt 2 = IF ( HASONEVALUE ( Category[Category] ), [Actual 2] - [Budget 2], BLANK () )
% Act vs Bgt 2 = iferror([Δ Act vs Bgt 2]/[Budget 2],0)
LY 2 = CALCULATE ( [Actual 2], SAMEPERIODLASTYEAR ( 'Calendar'[Reported Date] ) )
Δ Act vs LY 2 = IF(HASONEVALUE(Category[Category]), [Actual 2]-[LY 2],BLANK())
% Act vs LY 2 = iferror([Δ Act vs LY 2]/[LY 2],0)

revenues.PNG

This is my PBIX file.

 

Best Regards,

Icey

 

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

View solution in original post

Icey New Contributor
New Contributor

Re: Combine measures in Matrix visual and calculate variance by date and period

Hi @imar ,

 

Maybe you can try which in this PBIX file 'My Measures 2'. This method is a bit cumbersome. If I have a better way, I will reply you immediately. You can also open another post to let more people help you.

 

Best Regards,

Icey

View solution in original post

5 REPLIES 5
Icey New Contributor
New Contributor

Re: Combine measures in Matrix visual and calculate variance by date and period

Hi @imar ,

 

You can create your measures like so:

Actual 2 =
VAR t =
    UNION ( Financials, Operationals )
RETURN
    IF (
        HASONEVALUE ( Category[Category] ),
        SUMX ( FILTER ( t, [Period] = "Actual" ), [Value] ),
        DIVIDE (
            SUMX ( FILTER ( t, [Period] = "Actual" && [Category] = "Revenues" ), [Value] ),
            SUMX ( FILTER ( t, [Period] = "Actual" && [Category] = "Visitors" ), [Value] )
        )
    )
Budget 2 =
VAR t =
    UNION ( Financials, Operationals )
RETURN
    IF (
        HASONEVALUE ( Category[Category] ),
        SUMX ( FILTER ( t, [Period] = "Budget" ), [Value] ),
        DIVIDE (
            SUMX ( FILTER ( t, [Period] = "Budget" && [Category] = "Revenues" ), [Value] ),
            SUMX ( FILTER ( t, [Period] = "Budget" && [Category] = "Visitors" ), [Value] )
        )
    )
Δ Act vs Bgt 2 = IF ( HASONEVALUE ( Category[Category] ), [Actual 2] - [Budget 2], BLANK () )
% Act vs Bgt 2 = iferror([Δ Act vs Bgt 2]/[Budget 2],0)
LY 2 = CALCULATE ( [Actual 2], SAMEPERIODLASTYEAR ( 'Calendar'[Reported Date] ) )
Δ Act vs LY 2 = IF(HASONEVALUE(Category[Category]), [Actual 2]-[LY 2],BLANK())
% Act vs LY 2 = iferror([Δ Act vs LY 2]/[LY 2],0)

revenues.PNG

This is my PBIX file.

 

Best Regards,

Icey

 

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

View solution in original post

imar Frequent Visitor
Frequent Visitor

Re: Combine measures in Matrix visual and calculate variance by date and period

Hi @Icey ,

 

Thank you for looking into this.

Your help is much appreciated!

 

In my real case, I want to add 10 more ratios below the financial and operational data. I am wondering how this is going to work with the Row subtotals label approach you suggested.

For example, assume that I want to calculate Expenses per Visitor or other ratios like Average stay per visitor (data not currently provided in my excel file). In that case how are we going to add these ratios below the table you created?

To my understanding Row subtotals level works only for one ratio (Revenues per visitor). How about the rest suggested ratios?

Do you think there is a quicker way to model my data (e.g. Financials and Operationals in one table) to make power bi more efficient?

 

Thank you in advance for you time!

 

Best Regards,

Imar

Icey New Contributor
New Contributor

Re: Combine measures in Matrix visual and calculate variance by date and period

Hi @imar ,

 

For serveral ratios like 'Revenues per visitor', this method is not feasible. I am looking for other ways, and I will respond to you as soon as I make progress.

 

Best Regards,

Icey

 

Icey New Contributor
New Contributor

Re: Combine measures in Matrix visual and calculate variance by date and period

Hi @imar ,

 

Maybe you can try which in this PBIX file 'My Measures 2'. This method is a bit cumbersome. If I have a better way, I will reply you immediately. You can also open another post to let more people help you.

 

Best Regards,

Icey

View solution in original post

imar Frequent Visitor
Frequent Visitor

Re: Combine measures in Matrix visual and calculate variance by date and period

Hi @Icey ,

 

Once again I would like to thank you for your time!

Your posts have inspired me to work this futher. I came up with the following solution:

1. I created a table with all measures.

2. I used the switch function to select these measures.

My solution.pbix is saved in the same folder:

https://www.dropbox.com/sh/x2rzap374vven36/AACA94W9ZdKosUEbleaoZ-4ja?dl=0

 

Best Regards,

Imar

 

 

 

 

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 355 members 3,276 guests
Please welcome our newest community members: