Forum Discussion
Helper VSum specific Rows Then Divide Sum of One Column by Sum of Another Column
I'm struggling with a simple Sum and divide. How do I write a Total% measure and turn this Excel formula to DAX?
I've tried this function but I'm not getting the correct result
Col1 = CALCULATE([Col1_Total],ALL(MonthTable[Month]),OR(MonthTable[Month] = "April", MonthTable[Month] = "May"))
Col2 = CALCULATE([Col1_Total],ALL(MonthTable[Month]),OR(MonthTable[Month] = "April", MonthTable[Month] = "May"))
Total = [Col1_Total] / [Col2_Total]
Thank you
Ashish_Mathurv-lili6-msftthank you so much for your help :) , unfortunately I could not utilize the functions you provided. I ended up with the Variables statement instead and much tideous.
Measure 4 = VAR Col1_Apr = CALCULATE(COUNTA(Table1[ID]),FILTER(Table1, Table1 [Data]="March"),FILTER(Table1, Table1 [S Month]="April"),FILTER(Table1, Table1 [S Year]="2018")) VAR Col1_May = CALCULATE(COUNTA(Table1[ID]),FILTER(Table1,Table1[Data]="April"),FILTER(Table1,Table1[S Month]="May"),FILTER(Table1,Table1[S Year]="2018")) VAR Col2_Apr = CALCULATE(COUNTA(Table1[ID]),FILTER(Table1,Table1[Data]="May"),FILTER(Table1,Table1[S Month]="April"),FILTER(Table1,Table1[S Year]="2018")) VAR Col2_May = CALCULATE(COUNTA(Table1[ID]),FILTER(Table1,Table1[Data]="June"),FILTER(Table1,Table1[S Month]="May"),FILTER(Table1,Table1[S Year]="2018")) RETURN (Col2_Apr + Col2_May ) / (Col1_Apr + Col1_May)
Total Start% = SWITCH(TRUE(), MAX(MonthTable[Month]) = "April", CALCULATE([Measure4]), MAX(MonthTable[Month]) = "May", CALCULATE([Measure5]), MAX(MonthTable[Month]) = "June", CALCULATE([Measure6]))
8 Replies
v-lili6-msftCommunity Support
HI, Stuznet
You may try to use TOTALYTD Function or DATESYTD Function in your measure as below
Measure = TOTALYTD(SUM(Table1[Col2]),'Date'[Date])/TOTALYTD(SUM(Table1[Col1]),'Date'[Date])
Result:
By the way,
DATESYTD ( 'Date'[Date] )
It corresponds to a filter over the date column using FILTER called by CALCULATETABLE, such as in the following code:
CALCULATETABLE ( FILTER ( ALL ( 'Date'[Date] ), AND ( 'Date'[Date] <= MAX ( 'Date'[Date] ), YEAR ( 'Date'[Date] ) = YEAR ( MAX ( 'Date'[Date] ) ) ) ) )Best Regards,
Lin
StuznetAshish_Mathurv-lili6-msftthank you so much for your help :) , unfortunately I could not utilize the functions you provided. I ended up with the Variables statement instead and much tideous.
Measure 4 = VAR Col1_Apr = CALCULATE(COUNTA(Table1[ID]),FILTER(Table1, Table1 [Data]="March"),FILTER(Table1, Table1 [S Month]="April"),FILTER(Table1, Table1 [S Year]="2018")) VAR Col1_May = CALCULATE(COUNTA(Table1[ID]),FILTER(Table1,Table1[Data]="April"),FILTER(Table1,Table1[S Month]="May"),FILTER(Table1,Table1[S Year]="2018")) VAR Col2_Apr = CALCULATE(COUNTA(Table1[ID]),FILTER(Table1,Table1[Data]="May"),FILTER(Table1,Table1[S Month]="April"),FILTER(Table1,Table1[S Year]="2018")) VAR Col2_May = CALCULATE(COUNTA(Table1[ID]),FILTER(Table1,Table1[Data]="June"),FILTER(Table1,Table1[S Month]="May"),FILTER(Table1,Table1[S Year]="2018")) RETURN (Col2_Apr + Col2_May ) / (Col1_Apr + Col1_May)
Total Start% = SWITCH(TRUE(), MAX(MonthTable[Month]) = "April", CALCULATE([Measure4]), MAX(MonthTable[Month]) = "May", CALCULATE([Measure5]), MAX(MonthTable[Month]) = "June", CALCULATE([Measure6]))
- Ashish_Mathur
Super User
Hi,What do Col1 and Col2 represent? Are years? If yes, then which years? If not, then for which year is this data?- Stuznet
Hi Ashish_Mathur Col1 and Col2 represent count of IDs
- Ashish_Mathur
I understand that. But that has to for year(s), Cities(s), Stores(s). Please clarify.
