Constant Value with shifting time periods

[Anonymous]

Hi I am trying to writing a measure and currently running into trouble with the constant value. After calcuating total hours needed I need to divide by 40 for a weekly period, 160 for monthly, 1920 yearly to the number of employee as an esitmate. As the date values are used to create tables, slicers. The measure needs to be dynamic, see example example below:

Column1	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Total	Comments
	1000	2000	3000	4000	5000	6000	7000	8000	9000	10000	11000	12000	78000
Divide by 160	6.25	12.5	18.75	25	31.25	37.5	43.75	50	56.25	62.5	68.75	75	487.5	Error as total as neededs to be divided by 160 * 12
Other type of aggregaition													40.625	Correct Value 78000/1920
Lamps	26000	13.54167	This could be a years worth of data
Countertops	26000	13.54167
Trucks	26000	13.54167
Total	78000	40.625

End goal:

If Weekly period divdide by 40, total would be divided by 52*40

If monthly period divide by 160, total would be divided by 160*12

If yearly period divided by 1920, total would be divided by 1920 * number of years.

[v-danhe-msft]

Hi @Anonymous,

What is your desired result?

If your data is like below:

Column1	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Total	Comments
	1000	2000	3000	4000	5000	6000	7000	8000	9000	10000	11000	12000	78000

You could refer to below code in query editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PKc3NM1TSUfJKzAOSbqlJQNI3sQhIOhYUgdmVINnSPDCZAxIvTQeSwakFQNI/uQRI+uWXAUmX1GSlWJ1opbDEnNJUIN/QwMAASBlBKGMIZQKhTCGUGYQyh1AWEMoSQoG0g2moOYZgg2JjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t, #"(blank).6" = _t, #"(blank).7" = _t, #"(blank).8" = _t, #"(blank).9" = _t, #"(blank).10" = _t, #"(blank).11" = _t, #"(blank).12" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}, {"(blank).4", type text}, {"(blank).5", type text}, {"(blank).6", type text}, {"(blank).7", type text}, {"(blank).8", type text}, {"(blank).9", type text}, {"(blank).10", type text}, {"(blank).11", type text}, {"(blank).12", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", type text}, {"Jan", Int64.Type}, {"Feb", Int64.Type}, {"Mar", Int64.Type}, {"Apr", Int64.Type}, {"May", Int64.Type}, {"Jun", Int64.Type}, {"Jul", Int64.Type}, {"Aug", Int64.Type}, {"Sep", Int64.Type}, {"Oct", Int64.Type}, {"Nov", Int64.Type}, {"Dec", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {}, "Attribute", "Value"),
    #"Promoted Headers1" = Table.PromoteHeaders(#"Unpivoted Columns", [PromoteAllScalars=true]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers1",{{"Column1", type text}, {"Value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type2", "divdide by 40", each [Value]/40),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "divdide by 160", each [Value]/160),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "divdide by 1920", each [Value]/1920)
in
    #"Added Custom2"

Result:

If you want to show the result like below picture:

You could refer to below code in query editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PKc3NM1TSUfJKzAOSbqlJQNI3sQhIOhYUgdmVINnSPDCZAxIvTQeSwakFQNI/uQRI+uWXAUmX1GSlWJ1opbDEnNJUIN/QwMAASBlBKGMIZQKhTCGUGYQyh1AWEMoSQoG0g2moOYZgg2JjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t, #"(blank).6" = _t, #"(blank).7" = _t, #"(blank).8" = _t, #"(blank).9" = _t, #"(blank).10" = _t, #"(blank).11" = _t, #"(blank).12" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}, {"(blank).4", type text}, {"(blank).5", type text}, {"(blank).6", type text}, {"(blank).7", type text}, {"(blank).8", type text}, {"(blank).9", type text}, {"(blank).10", type text}, {"(blank).11", type text}, {"(blank).12", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", type text}, {"Jan", Int64.Type}, {"Feb", Int64.Type}, {"Mar", Int64.Type}, {"Apr", Int64.Type}, {"May", Int64.Type}, {"Jun", Int64.Type}, {"Jul", Int64.Type}, {"Aug", Int64.Type}, {"Sep", Int64.Type}, {"Oct", Int64.Type}, {"Nov", Int64.Type}, {"Dec", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {}, "Attribute", "Value"),
    #"Promoted Headers1" = Table.PromoteHeaders(#"Unpivoted Columns", [PromoteAllScalars=true]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers1",{{"Column1", type text}, {"Value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type2", "divdide by 40", each [Value]/40),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "divdide by 160", each [Value]/160),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "divdide by 1920", each [Value]/1920),
    #"Demoted Headers" = Table.DemoteHeaders(#"Added Custom2"),
    #"Changed Type3" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type3"),
    #"Promoted Headers2" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type4" = Table.TransformColumnTypes(#"Promoted Headers2",{{"Column1", type text}, {"Jan", type number}, {"Feb", type number}, {"Mar", type number}, {"Apr", type number}, {"May", type number}, {"Jun", type number}, {"Jul", type number}, {"Aug", type number}, {"Sep", type number}, {"Oct", type number}, {"Nov", type number}, {"Dec", type number}})
in
    #"Changed Type4"

You could also download the pbix file to have a view.

Regards,

Daniel He

[Anonymous]

Hi Dan,

Thanks for your response, I can complete in the query editor, but I have I am not sure how to complete in a DaX formula that is dynamic. The desired result you show is corrected but that needs to occur depenedning on if the filter is by aggeraate is by month, week, or year, 

[v-danhe-msft]

Hi @Anonymous,

I am afraid it could not solve it with dax due to the dax could not unpivot the different columns, I suggest you solve it with query editor and could you please post your deisred result by reason of I could not understand about the sentense "but that needs to occur depenedning on if the filter is by aggeraate is by month, week, or year" you have posted.

Regards,

Daniel He

[Anonymous]

Hi Dan, 

I apologize for the confusion I guess to state the question more generall for dax: is it possible to that when a weekly view is selected I divide by 40, if a monthly view is selected divide by 160, if year view is selected divide by 1980. I have a measure that calcules hours need but need to divide those hours into weeks or months or years dynamically.