Hello friends! I am relatively new to Pivot Tables, and just learned of DAX yesterday to help me with some analysis I am doing. Now, I have not been able to find this answer for another DAX measure field, and hope that someone will help me with what seems to be an easy question.
My source data has several thousand rows spanning some 4+ years. Since I cannot use live business data here, i have come up with an example of a Pivot table result I am hoping to create. In this example, I need to create a rolling 12-month table of car sales cars across 4 breakdowns (make, model, style, and color), and to create two percentage values. For each breakdown, I need to know the percentage of units against the parent, and the percentage of units against the grand total.
For the count field, I used this DAX formula:
=calculate(Count([Invoice#]),Filter(Range,Range[SaleDate] <= Today() && Range[SaleDate] > Today() - 365))
Here is the desired result:
----Data---- Count %Parent %Total
C-Make 55 55.00% 55.00%
TopModel 35 63.64% 35.00%
4-Wheels 30 85.71% 30.00%
Blue 18 60.00% 18.00%
White 12 40.00% 12.00%
No-Wheels 5 14.29% 5.00%
Red 1 20.00% 1.00%
White 4 80.00% 4.00%
Meh-Model 20 36.36% 20.00%
WithRoof 20 100.00% 20.00%
Black 20 100.00% 20.00%
F-Make 45 45.00% 45.00%
Best 45 100.00% 45.00%
Spicy 42 93.33% 42.00%
Red 22 52.38% 22.00%
Green 20 47.62% 20.00%
Boring 3 6.67% 3.00%
Black 3 100.00% 3.00%
Grand Totals 100 100.00% 100.00%
Can you help me with the definition/formula for the percent of parent and percent of grand total fields?
Incidentally, once I get this, I will also need to create another table for a rolling 1-month set of data.
Thanks for your help!
Don
My source data has several thousand rows spanning some 4+ years. Since I cannot use live business data here, i have come up with an example of a Pivot table result I am hoping to create. In this example, I need to create a rolling 12-month table of car sales cars across 4 breakdowns (make, model, style, and color), and to create two percentage values. For each breakdown, I need to know the percentage of units against the parent, and the percentage of units against the grand total.
For the count field, I used this DAX formula:
=calculate(Count([Invoice#]),Filter(Range,Range[SaleDate] <= Today() && Range[SaleDate] > Today() - 365))
Here is the desired result:
----Data---- Count %Parent %Total
C-Make 55 55.00% 55.00%
TopModel 35 63.64% 35.00%
4-Wheels 30 85.71% 30.00%
Blue 18 60.00% 18.00%
White 12 40.00% 12.00%
No-Wheels 5 14.29% 5.00%
Red 1 20.00% 1.00%
White 4 80.00% 4.00%
Meh-Model 20 36.36% 20.00%
WithRoof 20 100.00% 20.00%
Black 20 100.00% 20.00%
F-Make 45 45.00% 45.00%
Best 45 100.00% 45.00%
Spicy 42 93.33% 42.00%
Red 22 52.38% 22.00%
Green 20 47.62% 20.00%
Boring 3 6.67% 3.00%
Black 3 100.00% 3.00%
Grand Totals 100 100.00% 100.00%
Can you help me with the definition/formula for the percent of parent and percent of grand total fields?
Incidentally, once I get this, I will also need to create another table for a rolling 1-month set of data.
Thanks for your help!
Don