Forum Discussion

aktripathi2506's avatar
aktripathi2506
Helper IV
9 years ago

Calculating measure

Hi,   Week Item Count of Item avg sale for that item 1 121 46 133 1 122 1 57 1 123 6 54 1 124 12 96 1 125 26 66 1 126 11 114 1 127 53 135 1 128 ...
Need Help
GilbertQ's avatar
GilbertQ
Super User
9 years ago

Hi aktripathi2506,

 

I would suggest if possible to always look at creating measures before storing the data in each column. Reason being is that if you want to the slice your data with another field, or possibly just get the average for each week, and remove the items, using the column method will mean that you have to create a new column. Which takes time, is not efficient and can cause performance problems in certain instances.

 

I solved your issue by creating the following measures:

 

Counts = SUM('Table1'[Count of Item]) 
Avgs = SUM('Table1'[avg sale for that item])
Output = DIVIDE([Counts],[Avgs]) - Formatted as %

And this was the output, as you can see for the Table total it is the expected 18.92%, as well as you can have other visuals.

  • parry2k's avatar
    parry2k
    Super User
    9 years ago

    hi GilbertQ 

     

    I agreed with your approach somehwat but sometime it is not possible that you can create the data at source level, so it depends on the situation.

    • GilbertQ's avatar
      GilbertQ
      Super User
      9 years ago

      Hi parry2k

       

      That is true, but you can always edit the data in the Query Editor before you bring it into your Power BI Model.

       

      In doing it in this way, your data at source is not modified, but rather you make the changes in the Query Editor which is part of Power BI Desktop.