Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
Hi, create a calculated column with:
PercentofDaySales = DIVIDE ( Table1[Value of Units Sold], CALCULATE ( SUM ( Table1[Value of Units Sold] ), FILTER ( Table1, Table1[Date] = EARLIER ( Table1[Date] ) ) ) )
Regards
Victor
Lima - Peru
Hi, create a calculated column with:
PercentofDaySales = DIVIDE ( Table1[Value of Units Sold], CALCULATE ( SUM ( Table1[Value of Units Sold] ), FILTER ( Table1, Table1[Date] = EARLIER ( Table1[Date] ) ) ) )
Regards
Victor
Lima - Peru
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 = CALCULATE ( [Value of units sold], ALL ( Table[Product], Table[Product Color] ) ) RETURN 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.
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |