Sum specific Rows Then Divide Sum of One Column by Sum of Another Column

[Stuznet]

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 

[v-lili6-msft]

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

[Stuznet · Accepted solution]

@Ashish_Mathur@v-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]

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.

[Stuznet]

@Ashish_Mathursorry, lets use count of Cities

[Ashish_Mathur]

Hi,

You may refer to my solution in this PBI file.

Hope this helps.

[Stuznet]

@Ashish_MathurThank you for sharing your solution but it is almost close enough what I'm looking for. I just don't know how to rewrite your function. 

So here is my function.

Month Table just like your Month_Order table

January to December

Measure Col1

Col1 = SWITCH(TRUE(),

MAX(Table2[Month]) = "April",CALCULATE(
            COUNT(Table1[ID]),FILTER(DATA,[Start Month]="April"
                && [Start Year] = "2018"
                && [Data] = "March")),

MAX(Table2[Month]) = "May", CALCULATE(
            COUNT(Table1[ID]),FILTER(Table1,[Start Month]="May"
                && [Start Year] = "2018"
                && [Data] = "April")),

MAX ( Table2[month] ) = "June", CALCULATE (
            COUNT(Table1[ID]),FILTER (Table1,[Start Month]= "June"
                && [Start Year] = "2018"
                && [Data] = "May")))

Measure Col2

Col2 = SWITCH(TRUE(),

MAX(Table2[Month]) = "April",CALCULATE(
            COUNT(Table1[ID]),FILTER(Table1,[Finish Month]="April"
                && [Finish Year] = "2018"
                && [Data] = "May")),

MAX(Table2[Month]) = "May",CALCULATE(
            COUNT(Table1[ID]),FILTER(Table1,[Finish Month]="May"
                && [Finish Year] = "2018"
                && [Data] = "June")),

MAX(Table2[Month]) = "June",CALCULATE(
            COUNT(Table1[ID]),FILTER(Table1,[Finish Month]="June"
                && [Finish Year] = "2018"
                && [Data] = "July")))

Measure Total% 

Total % = [Col1Total] / [Col2Total]