Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
My data source has the first three columns shown below. I can't figure out how to create the two columns on the right. Essentially what I'm trying to do is create a price ratio for each month. 'Standard' is the baseline that other prices should be compared to. Any ideas?
Month | Group | Price | Standard for the month | % to Standard |
Jan | Basic | 1 | 1.25 | 80% |
Jan | Standard | 1.25 | 1.25 | 100% |
Jan | Premium | 1.5 | 1.25 | 120% |
Jan | Specialty | 1.75 | 1.25 | 140% |
Feb | Basic | 0.95 | 1.28 | 74% |
Feb | Standard | 1.28 | 1.28 | 100% |
Feb | Premium | 1.54 | 1.28 | 120% |
Feb | Specialty | 1.8 | 1.28 | 141% |
Mar | Basic | 1.02 | 1.3 | 78% |
Mar | Standard | 1.3 | 1.3 | 100% |
Mar | Premium | 1.58 | 1.3 | 122% |
Mar | Specialty | 1.82 | 1.3 | 140% |
Solved! Go to Solution.
Hi, create the calculated columns:
1: Standard Column
Standard = CALCULATE ( VALUES ( Table1[Price] ), FILTER ( ALLEXCEPT ( Table1, Table1[Month] ), Table1[Group] = "Standard" ) )
2. % to Standard Column
% To Standard = Divide(Table1[Price],Table1[Standard])
Ready.
Hi, create the calculated columns:
1: Standard Column
Standard = CALCULATE ( VALUES ( Table1[Price] ), FILTER ( ALLEXCEPT ( Table1, Table1[Month] ), Table1[Group] = "Standard" ) )
2. % to Standard Column
% To Standard = Divide(Table1[Price],Table1[Standard])
Ready.
You are a genius!! Thanks so much for the quick and helpful reply.
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |