Power BI: Creating a financial model

[hidenseek9]

Hello Power BI community!

I have a project to build a financial model in Power BI.

What I would like to know is that if there a way to select values from

many Slicer visuals and have power BI calculate numbers based on a measure, I set up.

For example, let's say I have 3 slicer visuals.

They all contain a value from 0 to 100.

If I select a value, 20 on one slicer, 30 on the second slicer, and 100 on the third slicer,

can I have a measure set up to calculate a product of these three values? (In this case, 20*30*100=60,000)

I have created a very simple dummy data below.

I would like to set up a measure that will calculate VOLUME * COST

so that when I select 60 on VOLUME and 100 on COST in slicer visuals or any other visuals

the measure will give me back 6,000.

Is this possible?

Dummy Data

Many thanks,

H

[v-caliao-msft]

@hidenseek9,

Please create two tables by using the DAX below.
Volume = VALUES(Table1[Volume (Ton)])
Cost = VALUES(Table1[Cost])

Create a measure in original table
Measure =
var costvalue = if(HASONEFILTER(Cost[Cost]),MAX(Cost[Cost]),BLANK())
var volumevalue = IF(HASONEFILTER(Volume[Volume (Ton)]),MAX(Volume[Volume (Ton)]),BLANK())
return costvalue*volumevalue

Regards,

Charlie Liao

[hidenseek9]

@v-caliao-msft

Awesome! It works! Thank you for this.

However, I am facing one issue.

I want to show YTD actual PL (Jan-Aug) and would like to attach current month (right now, September)

to the PL picture.

Since I am having a difficulty combining actual and forecast together in one table,

I am showing the actual and forecast side by side as below.

The problem that i have is that using the dropdown filter to choose a value,

the number shows up on every month.

For instance, AC retailer I chose -21.0 and it is showing every month

when I want to show in just a current month (in this case September)

Any way I can achieve this?

Many thanks,

H

[hidenseek9]

Could anybody help me with the problem I posted above and below?

Thanks,

H

---------------------

Awesome! It works! Thank you for this.

However, I am facing one issue.

I want to show YTD actual PL (Jan-Aug) and would like to attach current month (right now, September)

to the PL picture.

Since I am having a difficulty combining actual and forecast together in one table,

I am showing the actual and forecast side by side as below.

The problem that i have is that using the dropdown filter to choose a value,

the number shows up on every month.

For instance, AC retailer I chose -21.0 and it is showing every month

when I want to show in just a current month (in this case September)

Any way I can achieve this?

Many thanks,

H

---------------------

[Ashish_Mathur]

Hi,

Share the link from where i can download the file.

[hidenseek9]

Thank you for offering to help.

Please find the link below.

Hope you can see the file.

FInancial Model Dashboard

Many thanks,

H

[Ashish_Mathur]

Hi,

I cannot download the file from there.

[hidenseek9]

Hi,

I will give you the access to the folder where my excel file and

Power BI .pbix are.

Hope you can access.

Folder

Many thanks,

H

[Ashish_Mathur]

Hi,

That takes me to a sign-in page.

[hidenseek9]

Hi,

This should work.

Sorry about this.

Folder

Many thanks,

H

[Ashish_Mathur]

Hi,

I got your files but i cannot understand your question.

[hidenseek9]

@Ashish_Mathur

If you open my Power BI dashboard, "CANN Closing", on Page1, I have three matrix.

I am having an issue with the matrix on the far right,

which is shown below.

Basically, in this matrix, what I want to do is to make an adjustment in the matrix.

There are items such as "Mitsubishi (adjustment)" or "Center Fee (adjustment)", that I choose

values from a pull down at the top.

Those are the adjustment that I want to make.

When I choose values from the pull down, the values show up

in all columns (Every single month).

However, since I am only simulating the PL for a month of September,

in this instance, I want the matrix to show just September.

How can I achieve this while I keeping the other two matrix as they are?

Hope the explanation above is clear.

Many thanks,

H

[CheenuSing · Accepted solution]

Hi @hidenseek9

Not sure of the values you are putting on the different matrix.

WHat I observe on the third matrix table all other months except September have the Volume(CM) measure as zero. So if you set the visual filter for this measure is not equal to zero , then you get only for September. 

Try it out.

Cheers

CheenuSing

[hidenseek9]

@CheenuSing

Oh, that was simple enough!

it works.

Thanks,

H