Forum Discussion

hidenseek9's avatar
hidenseek9
Icon for Post Patron rankPost Patron
9 years ago
Solved

Power BI: Creating a financial model

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

 

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    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

13 Replies

  • v-caliao-msft's avatar
    v-caliao-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    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's avatar
      hidenseek9
      Icon for Post Patron rankPost Patron
      8 years ago

      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's avatar
        hidenseek9
        Icon for Post Patron rankPost Patron
        8 years ago

        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

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