I am working on getting the BI formated so i can connect to a server for data instead of using excel. One of my main formulas is using percentages based on sales per day. I believe i need to make a new column, but i can figure out the formula to do so. Here is an example of the type of data i am looking at:
Date Product Product Color Value of units sold Percent of day sales
8/16/2017 Apples Red $50 50%
8/16/2017 Apples Green $25 25%
8/16/2017 Berries Black $10 10%
8/16/2017 Berries Blue $15 15%
8/17/2017 Apples Red $40 40%
8/17/2017 Apples Green $35 35%
8/17/2017 Berries Black $5 5%
8/17/2017 Berries Blue $20 20%
The last column is what i cannot fiqure out, so any help would be great.
Are Date, Product, Product Color all from the same table? Is Value of units sold a measure; and what table is it derived from? Are you looking for a measure for Percent of day Sales? Without some details on model design it is difficult to provide formula needed.
This may work making assumptions about model normalization:
Percent of day sales =
VAR value_today = [Value of units sold]
VAR all_sales_today =
[Value of units sold],
ALL ( Table[Product], Table[Product Color] )
DIVIDE ( value_today, all_sales_today )
Sorry about the confustion. Date, product, product color are from the same table. Value of units sold is also on the same table. I dont think a measure would work (i could be wrong), becasue i need the percentages to stay the same regardless of report filters. I think it just needs to be an additional column. Sorry, im still new to this, excel is what i am used to.