Building a summary table with measure results as the row values

[ldavis4230]

Hi!

I have a measure that computes a Change Type as below:

Price/Utilization Change Type = SELECTCOLUMNS(SUMMARIZE(Purchase,Facility[Facility],'Product'[Category],'Product'[SKU],

"PriceChange",IF([Price Impact, Sum]>0,"Price Increase",IF([Price Impact, Sum]<0,"Price Decrease","No Price Change")),

"UtilizationChange",IF([Utilization Impact, Sum]>0,"Utilization Increase",IF([Utilization Impact, Sum]<0,"Utilization Decrease","No Utilization Change"))),

"ChangeType",CONCATENATE([PriceChange],CONCATENATE(", ",[UtilizationChange])))

I now want to create a summary table that groups values by the Price/Utilization Change Type, with the output values of this measure listed as the row values:

Price/Utilization Change Type
No Price Change, No Utilization Change
No Price Change, Utilization Increase
No Price Change, Utilization Decrease
Price Increase, Utilization Increase
Price Increase, Utilization Decrease
Price Decrease, Utilization Increase
Price Decrease, Utilization Decrease
Price Increase, No Utilization Change
Price Decrease, No Utilization Change

Any thoughts on a way to do this? I've previously used a calculated column instead of a measure, but am now enabling the user to change the results based upon slicer selections, so it needs to be dynamic.

Thank you!

Lauren

[dax]

Hi @ldavis4230 , 

If you want to change this to measure , you could try to refer to below Measure to see whetehr it work or not

name     id    amount     v

a	1	-5	1
a	2	-7	2
a	3	-9	-3
a	4	1	4
a	5	4	1
b	1	2	2
b	2	-2	4
b	3	-3	-10
b	4	5	2
c	1	-1	1
c	2	2	2
c	3	1	5
c	4	2	3

Measure 2 = VAR TEMP=SUMMARIZE(T2,T2[name],T2[id],"T1", IF(SUM(T2[amount])>0, "Price Decrease","No Price Change"), "T2",IF(SUM(T2[v])>0, "Utilization Decrease","No Utilization Change")) VAR ADC=  ADDCOLUMNS(TEMP,"COMBINE",MIN([T1]) &","& MIN([T2]))  RETURN MINX(ADC,[COMBINE])

Or you also could upload your file and inform me your expected output. Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ldavis4230]

Thanks @rajulshah & @dax for your replies! I'm sorry my requirement was unclear.

I have built a measure (Price/Utilization Change Type) that has a finite list of results.

It is currently used as a column in a detail table like the below:

Current table with Change Type measure as column

Now I want to build a matrix that uses the measure results as the row values, and allow me to summarize the rest of my data by the Change Type:

Desired result with measure as a the row value

So in short, is there a way to build a matrix using a measure as the row value? Or do you have any suggestions as a workaround to simulate this?

I think building the Change Type as a calculated column is the obvious answer, however that won't meet my requirement; I need the measure results to change dynamically based upon slicer selections.

Thanks so much!

Lauren

[rajulshah]

Hello @ldavis4230,

Please try the following DAX for calculated table:

Utilization Type = 
VAR PriceImpactSum = CALCULATE(SUM('Table'[Price Impact]))
VAR UtilImpactSum = CALCULATE(SUM('Table'[Util Impact]))
VAR UtilTypeTable = ADDCOLUMNS(SUMMARIZE('Table','Table'[Facility],'Table'[Category],'Table'[SKU],'Table'[Price Impact],'Table'[Util Impact],"PriceChange",IF(CALCULATE(SUM('Table'[Price Impact]))>0,"Price Increase",IF(CALCULATE(SUM('Table'[Price Impact]))<0,"Price Decrease","No Price Change")),
"UtilizationChange",IF(CALCULATE(SUM('Table'[Util Impact]))>0,"Utilization Increase",IF(CALCULATE(SUM('Table'[Util Impact]))<0,"Utilization Decrease","No Utilization Change"))),"ChangeType",CONCATENATE([PriceChange],CONCATENATE(", ",[UtilizationChange])))
RETURN SUMMARIZE(UtilTypeTable,[ChangeType],"Price Impact Sum",CALCULATE(SUM('Table'[Price Impact])),"Util Impact Sum",SUM('Table'[Util Impact]))

Please note that you need to change sum and use your measure instead.

Hope this helps.

[rajulshah]

Hello @ldavis4230,

I am sorry I was not able to understand the requirement.

Can you please provide the expected data vs. data now?