Forum Discussion
Adding Quarter to Quarter Values
Hello Team,
I have two dataset Sales Table and Target Table and Quarter reference Table.
Sales Table
| Company | Quarter | Sales |
| ABC | Q1 | 124.00 |
| BCD | Q1 | 234.00 |
| CDE | Q1 | 24325.00 |
| DEF | Q1 | 5687.00 |
| EFG | Q1 | 23457.00 |
| FGH | Q1 | 25.00 |
| GHI | Q1 | 34.00 |
| IJK | Q1 | 6543.00 |
| ABC | Q2 | 5466.00 |
| BCD | Q2 | 7654.00 |
| CDE | Q2 | 235.00 |
| DEF | Q2 | 6543.00 |
| EFG | Q2 | 5432.00 |
| FGH | Q2 | 524635.00 |
| GHI | Q2 | 543.00 |
| IJK | Q2 | 345.00 |
| ABC | Q3 | 576.00 |
| BCD | Q3 | 46354.00 |
| CDE | Q3 | 65.00 |
| DEF | Q3 | 98.00 |
| EFG | Q3 | 4635.00 |
| FGH | Q3 | 34.00 |
| GHI | Q3 | 6543.00 |
| IJK | Q3 | 45.00 |
| ABC | Q4 | 32543.00 |
| BCD | Q4 | 3456.00 |
| CDE | Q4 | 3456.00 |
| DEF | Q4 | 345.00 |
| EFG | Q4 | 1233.00 |
| FGH | Q4 | 5.00 |
| GHI | Q4 | 2364.00 |
| IJK | Q4 | 76543.00 |
Target Table
| Service Line | Quarter | target |
| ABC | Q1 | 5432.00 |
| BCD | Q1 | 5432.00 |
| CDE | Q1 | 5432.00 |
| DEF | Q1 | 5432.00 |
| EFG | Q1 | 5432.00 |
| FGH | Q1 | 5432.00 |
| GHI | Q1 | 5432.00 |
| IJK | Q1 | 5432.00 |
| ABC | Q2 | 7654.00 |
| BCD | Q2 | 7654.00 |
| CDE | Q2 | 7654.00 |
| DEF | Q2 | 7654.00 |
| EFG | Q2 | 7654.00 |
| FGH | Q2 | 7654.00 |
| GHI | Q2 | 7654.00 |
| IJK | Q2 | 7654.00 |
| ABC | Q3 | 87654.00 |
| BCD | Q3 | 87654.00 |
| CDE | Q3 | 87654.00 |
| DEF | Q3 | 87654.00 |
| EFG | Q3 | 87654.00 |
| FGH | Q3 | 87654.00 |
| GHI | Q3 | 87654.00 |
| IJK | Q3 | 87654.00 |
| ABC | Q4 | 43244.00 |
| BCD | Q4 | 43244.00 |
| CDE | Q4 | 43244.00 |
| DEF | Q4 | 43244.00 |
| EFG | Q4 | 43244.00 |
| FGH | Q4 | 43244.00 |
| GHI | Q4 | 43244.00 |
| IJK | Q4 | 43244.00 |
Quarter Table
| Quarter |
| Q1 |
| Q2 |
| Q3 |
| Q4 |
I related three tables with Quarter column.
When I create Clustered column chart visual X axis - Quarter from Refernce table Y axis - Sum of Sales, Sum of Target.
I am getting like this
But I need output as below screenshot in power bi,
Thanks,
Hariharan
8 Replies
Hi.
EDITED: this solution does not work since you dont have Date and sales in the same table.
See updated solution.
From what you describe, you want to create columns witch cumulative sums for
- Sales
- Target Sales
Try adopting this DAX expression to your data in order to calculate the cumulative sum of Sales.// Measure with cumulative sum of sales cumulative Sum of sales by Quarter = TOTALYTD( SUM('YourTable'[Sales]), 'YourTable'[Date], ALL('YourTable'), "Q" //can be changed to "M" for month, "D" for day )
You can also see a similar solved problem (cumulative sum) here:
https://community.fabric.microsoft.com/t5/Desktop/Cumulative-values-Sum-of-gt-Count-of-with-Strings/m-p/3297350#M1103112
Did this solve your problem Hariharank04
If so, mark as accepted solution so it is easier for others to find the answer.
Kind regardsHi Anonymous ,
We have only Quarter column in the Dataset, don't have date column (Month, Year, Day).
Thanks,
Hariharan
Hi.
Sorry, my fault. I now see that you only have Quarter.
You can use the below updated DAX expression to compute cumulative sales by Quarter:
(Solution assuming table name = Sales and columnname = Sales )Cumulative Sum of sales by Quarter = VAR CurrentQuarter = MAX(Sales[Quarter]) RETURN CALCULATE ( SUM ( Sales[Sales] ), FILTER ( ALL(Sales), Sales[Quarter] <= CurrentQuarter ) ), to produce something like this:
You can reuse the same DAX expression for your table "Target", replacing to correct tablename and columnname.
Hariharank04 - did this resolve your problem?Hi Anonymous ,
The above measure is working fine but in my data (Sales & Target) I have three more column - Region, Location, Product. IF I use this three fields in slicer to see the data. Cumulative visual is not changing according to that. Can you help me to fix this too?
Thanks for swift response.
Regards,
Hariharan
Sales Data
Company Quarter Region Location Product Sales ABC Q1 A Canada Table 124.00 BCD Q1 B Bulgaria Chair 234.00 CDE Q1 C Portugal Lamp 24325.00 DEF Q1 B Brazil Clock 5687.00 EFG Q1 C India Table 23457.00 FGH Q1 A China Chair 25.00 GHI Q1 B Australia Lamp 34.00 IJK Q1 A Japan Clock 6543.00 ABC Q2 A Canada Chair 5466.00 BCD Q2 B Bulgaria Lamp 7654.00 CDE Q2 C Portugal Clock 235.00 DEF Q2 B Brazil Table 6543.00 EFG Q2 C India Chair 5432.00 FGH Q2 A China Lamp 524635.00 GHI Q2 B Australia Clock 543.00 IJK Q2 A Japan Lamp 345.00 ABC Q3 A Japan Clock 576.00 BCD Q3 B Japan Table 46354.00 CDE Q3 C Canada Chair 65.00 DEF Q3 B Bulgaria Lamp 98.00 EFG Q3 C Portugal Clock 4635.00 FGH Q3 A Brazil Table 34.00 GHI Q3 B India Chair 6543.00 IJK Q3 A China Lamp 45.00 ABC Q4 A Australia Clock 32543.00 BCD Q4 B Japan Table 3456.00 CDE Q4 C Portugal Chair 3456.00 DEF Q4 B Brazil Lamp 345.00 EFG Q4 C India Table 1233.00 FGH Q4 A China Chair 5.00 GHI Q4 B Australia Lamp 2364.00 IJK Q4 A Japan Clock 76543.00 Target Data
Service Line Quarter Region Location Product target ABC Q1 B Canada Table 5432.00 BCD Q1 C Bulgaria Chair 5432.00 CDE Q1 B Portugal Lamp 5432.00 DEF Q1 C Brazil Clock 5432.00 EFG Q1 A India Table 5432.00 FGH Q1 B China Chair 5432.00 GHI Q1 A Australia Lamp 5432.00 IJK Q1 A Japan Clock 5432.00 ABC Q2 B Canada Chair 7654.00 BCD Q2 C Bulgaria Lamp 7654.00 CDE Q2 B Portugal Clock 7654.00 DEF Q2 C Brazil Table 7654.00 EFG Q2 A India Chair 7654.00 FGH Q2 B China Lamp 7654.00 GHI Q2 A Australia Clock 7654.00 IJK Q2 A Japan Lamp 7654.00 ABC Q3 B Japan Clock 87654.00 BCD Q3 C Japan Table 87654.00 CDE Q3 B Canada Chair 87654.00 DEF Q3 C Bulgaria Lamp 87654.00 EFG Q3 A Portugal Clock 87654.00 FGH Q3 B Brazil Table 87654.00 GHI Q3 A India Chair 87654.00 IJK Q3 A China Lamp 87654.00 ABC Q4 B Australia Clock 43244.00 BCD Q4 C Japan Table 43244.00 CDE Q4 B Portugal Chair 43244.00 DEF Q4 C Brazil Lamp 43244.00 EFG Q4 A India Table 43244.00 FGH Q4 B China Chair 43244.00 GHI Q4 A Australia Lamp 43244.00 IJK Q4 A Japan Clock 43244.00
