MERGE OF MULTIPLE MEASURES

[srkase]

Hi all,

I have measures like this...

TQ1516SASON = CALCULATE(SUMX(PROD_CABLE,PROD_CABLE[APR]+PROD_CABLE[MAY]+PROD_CABLE[JUN]+PROD_CABLE[JUL]+PROD_CABLE[AUG]+PROD_CABLE[SEP]),PROD_CABLE[YEAR]="20152016")

TQ1617SASON = CALCULATE(SUMX(PROD_CABLE,PROD_CABLE[APR]+PROD_CABLE[MAY]+PROD_CABLE[JUN]+PROD_CABLE[JUL]+PROD_CABLE[AUG]+PROD_CABLE[SEP]),PROD_CABLE[YEAR]="20162017")

TQ1718SASON = CALCULATE(SUMX(PROD_CABLE,PROD_CABLE[APR]+PROD_CABLE[MAY]+PROD_CABLE[JUN]+PROD_CABLE[JUL]+PROD_CABLE[AUG]+PROD_CABLE[SEP]),PROD_CABLE[YEAR]="20172018")

TQ1819SASON = CALCULATE(SUMX(PROD_CABLE,PROD_CABLE[APR]+PROD_CABLE[MAY]+PROD_CABLE[JUN]+PROD_CABLE[JUL]+PROD_CABLE[AUG]+PROD_CABLE[SEP]),PROD_CABLE[YEAR]="20182019")

TQ1920SASON = CALCULATE(SUMX(PROD_CABLE,PROD_CABLE[APR]+PROD_CABLE[MAY]+PROD_CABLE[JUN]+PROD_CABLE[JUL]+PROD_CABLE[AUG]+PROD_CABLE[SEP]),PROD_CABLE[YEAR]="20192020")

I need a single query to show bring in all the measures...

Is it possible ?

Thanks in advance..

REgards

SRK

[AnthonyTilley]

Can you provide a little more detail as to what you are trying to achive, along with some sample data 

you can of course use multiple measures together but it will depend on what your desired outcome is 

Regards,

Anthony

[srkase]

---

@AnthonyTilley wrote:

Can you provide a little more detail as to what you are trying to achive, along with some sample data 

you can of course use multiple measures together but it will depend on what your desired outcome is 

Regards,

Anthony

---

this was my data

YEAR	SHORTNAME	DEALER	TYPE	PRODUCT	APR	MAY	JUN	JUL	AUG	SEP	OCT	NOV	DEC	JAN	FEB	MAR	TOT	CATEGORY
20182019	AB	A	STD	PROD	10	0	0	0	0	0	0	0	0	0	0	0	10	PRODUCT
20182019	AC	B	STD	PROD	20	0	0	0	0	0	0	0	0	0	0	0	20	PRODUCT
20182019	AD	A	STD	PROD	30	0	0	0	0	0	0	0	0	0	0	0	30	PRODUCT
20182019	AB	A	STD	CAB	0	40	10	0	0	0	0	0	0	0	0	0	50	CAB
20182019	AB	A	STD	CAB	0	50	10	0	0	0	0	0	0	0	0	0	60	CAB
20182019	AC	A	STD	CAB	0	0	0	50	50	50	50	50	50	50	50	50	450	CAB
20182019	AD	A	STD	CAB	0	0	0	50	50	50	50	50	50	50	50	50	450	CAB
20192020	AB	A	STD	PROD	10	0	0	0	0	0	0	0	0	0	0	0	10	PRODUCT
20192020	AC	B	STD	PROD	20	0	0	0	0	0	0	0	0	0	0	0	20	PRODUCT
20192020	AD	A	STD	PROD	30	0	0	0	0	0	0	0	0	0	0	0	30	PRODUCT
20192020	AB	A	STD	CAB	0	40	10	0	0	0	0	0	0	0	0	0	50	CAB
20192020	AB	A	STD	CAB	0	50	10	0	0	0	0	0	0	0	0	0	60	CAB
20192020	AC	A	STD	CAB	0	0	0	50	50	50	50	50	50	50	50	50	450	CAB
20192020	AD	A	STD	CAB	0	0	0	50	50	50	50	50	50	50	50	50	450	CAB

I would like to compare as on value and qty this time last year.. For that i need to create the combined measure which i am having separately now..

[v-juanli-msft]

Hi @srkase 

Here is my final output, is it something you expect?

If so, In Edit queries, unpivot columns, then add custom columns

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtABiSyUdJUcnEAHEwSEuQDIgyB9EGRoACVIwWANIc6hziFKsDqoVzkDCCd0KI1KtMMJnhQs2XxiTaoUxPitQA8oZzAepN4H7nxhsagDVTKzxpiQZb4bLeGccxqO4i1jCBKcvXGhqjaWRATgV0DDRwq2gXaKFW0G7RIsjoKiVaAkaT1miRYkDOqQmWiXaWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [EAR = _t, SHORTNAME = _t, DEALER = _t, TYPE = _t, PRODUCT = _t, APR = _t, MAY = _t, JUN = _t, JUL = _t, AUG = _t, SEP = _t, OCT = _t, NOV = _t, DEC = _t, JAN = _t, FEB = _t, MAR = _t, TOT = _t, CATEGORY = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"EAR", Int64.Type}, {"SHORTNAME", type text}, {"DEALER", type text}, {"TYPE", type text}, {"PRODUCT", type text}, {"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}, {"JAN", Int64.Type}, {"FEB", Int64.Type}, {"MAR", Int64.Type}, {"TOT", Int64.Type}, {"CATEGORY", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"TOT"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"EAR", "SHORTNAME", "DEALER", "TYPE", "PRODUCT", "CATEGORY"}, "Attribute", "Value"),
    #"Added Conditional Column" = Table.AddColumn(#"Unpivoted Columns", "month.no", each if [Attribute] = "APR" then 4 else if [Attribute] = "MAY" then 5 else if [Attribute] = "JUN" then 6 else if [Attribute] = "JUL" then 7 else if [Attribute] = "AUG" then 8 else if [Attribute] = "SEP" then 9 else if [Attribute] = "OCT" then 10 else if [Attribute] = "NOV" then 11 else if [Attribute] = "DEC" then 12 else if [Attribute] = "JAN" then 1 else if [Attribute] = "FEB" then 2 else if [Attribute] = "MAR" then 3 else null),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "year", each if [month.no] >= 4 then Text.Start(Text.From([EAR], "en-US"), 4) else  Text.End(Text.From([EAR], "en-US"), 4))
in
    #"Added Conditional Column1"

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-juanli-msft]

Hi @srkase 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case?

If not, please let me know.

Best Regards

Maggie

[srkase]

I need not want to unpivot... Because it creates more than 60 crore records and it is a time consuming process each time the data is refreshed from excel..

without unpivoting is there any option?

[AnthonyTilley]

aplogise if this is frustrating but im still not sure what you are trying to acheive.

from your data and measures it loos as tho you have calculated a single meaasher for teh total for each of the years

Would it not be easier to just create one measure regardless of year for example total 

Y TOTAL= CALCULATE(SUMX(PROD_CABLE,PROD_CABLE[APR]+PROD_CABLE[MAY]+PROD_CABLE[JUN]+PROD_CABLE[JUL]+PROD_CABLE[AUG]+PROD_CABLE[SEP]))

and then ues the year colunm as a split table.

i would then surgest eaither changing your colunm or creating a new one for year so that you have only one number for example 20182019 whould just be 2018

you can then use this colunm to create all manner of