Forum Discussion

cshowe80's avatar
cshowe80
Helper I
9 years ago

Summing Multiple New Meassures

I've got a new issue I'm looking to resolve with the help of the PowerBI gurus here. I could be doing this incorrectly so please give me any feedback you feel is important. 

 

I've got a new report I'm designing and one of the elements to it is prediction of future revenue potential. For this I have essentially 4 different products, all with individual rates. The system generates the forecast which included the number of each unit that's projected to be sold. The rates are as follows

 

1 @ $25

2 @$20

1@$40

 

Now in the data I'm hoping to summarize all the projected unit revenues and then sum those up into one single number. I was thinking to build a new meassure for each unit and then sum up the four new meassures to get the single revenue number. In reviewing this method I am thinking I've over comlicated things. Is there a more simplistic method of achieving this?

 

Here is a sample of the data for ease of explaination. 'Ad Request Sizes' are products and 'Forecasted Impressions' the number of units projected to be sold. 

 

Ad Request Size are "Products"

Need Help

3 Replies

  • Vvelarde's avatar
    Vvelarde
    Community Champion
    9 years ago

    cshowe80

     

    Hello, Create a calculated column for the rate for each product.

     

    RateProduct=Switch('Table1'[Ad Request Size]);1;25;2;20;3;40;4;50)

     

    Create a measure:

     

    TotalProjectedRevenue=Sumx('Table1';'Table1'[ForecastedImpressions]*'Table1'[RateProduct])

     

     

     

     

    • cshowe80's avatar
      cshowe80
      Helper I
      9 years ago

      Sorry to be so dense but I've never done calculated columns before and I'm poor at DAX at the moment. As I'm sure my posts show I'm abit of a novice working towards more proficiency. I've got a tab in my excel report (the data source for this PowerBI report) named Creative Map. This tab is to map the ad request sizes (one of which shows multiple possibilities) to new sizes. In this table I also have rates for each of these items. 

       

      Am I able to use these rates in this table and apply them to the other table which has the forecasted sales of each of these items? 

      • v-ljerr-msft's avatar
        v-ljerr-msft
        Microsoft Employee
        9 years ago

        Hi cshowe80,

        Am I able to use these rates in this table and apply them to the other table which has the forecasted sales of each of these items? 

        Yes, you are. First you need to create a relationship between these two tables, then you should be able to use RELATED Function (DAX) to create the measure like below.

        TotalProjectedRevenue =
SUMX (
    'SalesTable';
    'SalesTable'[ForecastedImpressions] * RELATED ( 'Creative Map'[RateProduct] )
)

        Regards