Create a new table with 12 calculated measure values in a new column

[Psycho]

Hello!

My Problem:

I have a table with 12 calculated measure values. Now I want this 12 measure show in a line diagram. I want to create a new table with 2 columns (month, calculated measures values). Is it possible to give a cell a direct Link to a measure (like Slink)? Or how can I solve this problem?

Thanks

[v-shex-msft · Accepted solution]

Hi @Psycho,

You can open the query editor and use "Unpivot Columns" function to work through the issue.

Sample:

Use duplicate function to backup this table:

Choose month columns and click on "Unpivot Columns" to merge them:

Result:

After above steps, you can continue the unfinished work.

Regards,

Xiaoxin Sheng

[Psycho · Accepted solution]

Yeah, that is a good solution. But there is my next problem.  🙂

now I have to do completly new calculations. (a lot of them). Do you have a Idea?

I want a calculate like this: 

02 Dur.Bestmge.AktJ = if(Tabelle2[Monat] = "End.Best.Mge.AktJ.02" ; ....

....   (the  value [Bestand] of this row + the value [Bestand] of that row, with the same value of the column [Artikelnummer] and the value "End.Best.Mge.AktJ 01")/2

this is harder than the other problem ^^

Youre idea with:

"SWITCH([Date].[MonthNo],1,[Sum of Jan],2,[Sum of Feb],3,[Sum of Mar],0))"

is very good and it worked. But unfortunately does not react to filter in the first table. Its ever the completly value. Now I must unpivot.  😞

[v-shex-msft]

Hi @Psycho,

According to your description, you want to get the monthly summary table, right?

If as I said, you can refer to below steps:

Sample table:

Calculate table formula(index column is use to sort the visual):

Table = DISTINCT(SELECTCOLUMNS(Sheet1,"Month",[Date].[Month],"Amount",SUMX(FILTER(ALL(Sheet1),Sheet1[Date].[Month]=EARLIER(Sheet1[Date].[Month])),Sheet1[Amount]),"Index",[Date].[MonthNo]))

Create visual:

>>Is it possible to give a cell a direct Link to a measure (like Slink)? 

You can use switch function to choose the value of measure, for example(I have created three measures to calculate the specific month amount):

Table 2 = DISTINCT(SELECTCOLUMNS(Sheet1,"Month",[Date].[Month],"Measure value",SWITCH([Date].[MonthNo],1,[Sum of Jan],2,[Sum of Feb],3,[Sum of Mar],0)))

Regards,

Xiaoxin Sheng

[Psycho]

Hello, Xiaxin! Thanks to you for your approach. The problem is, my starting table has no date column or any date values. But 12 of 20 column contains monthly observation values. So I can not make a direct connection to Sheet1 with a date. From these 12 columns, (and a lot of calculated columns) I I calculate 12 values. One per month. And this values must in a Line Diagram.

[v-shex-msft · Accepted solution]

Hi @Psycho,

You can open the query editor and use "Unpivot Columns" function to work through the issue.

Sample:

Use duplicate function to backup this table:

Choose month columns and click on "Unpivot Columns" to merge them:

Result:

After above steps, you can continue the unfinished work.

Regards,

Xiaoxin Sheng

[Psycho · Accepted solution]

Yeah, that is a good solution. But there is my next problem.  🙂

now I have to do completly new calculations. (a lot of them). Do you have a Idea?

I want a calculate like this: 

02 Dur.Bestmge.AktJ = if(Tabelle2[Monat] = "End.Best.Mge.AktJ.02" ; ....

....   (the  value [Bestand] of this row + the value [Bestand] of that row, with the same value of the column [Artikelnummer] and the value "End.Best.Mge.AktJ 01")/2

this is harder than the other problem ^^

Youre idea with:

"SWITCH([Date].[MonthNo],1,[Sum of Jan],2,[Sum of Feb],3,[Sum of Mar],0))"

is very good and it worked. But unfortunately does not react to filter in the first table. Its ever the completly value. Now I must unpivot.  😞

[v-shex-msft]

Hi @Psycho,

Sorry for slow response, you can try to use the merged column to calculate.(But I think unpivot not a effective way, it will create a lot of Redundant data)

>>"But unfortunately does not react to filter in the first table"

It not appeared on my side, can you share me a simple sample, I will test it.

Regards,

Xiaoxin Sheng

[Psycho]

Yes of curse. Here e sample. I want to show the values "Monats LUG" in a Linediagram. It works for the unfiltered falue.

http://www.file-upload.net/download-12038951/TestLUG2-Kopie.pbix.html

[v-shex-msft]

Hi @Psycho,

Can you share it to 1drive?

Regards,

Xiaoxin Sheng

[Psycho]

Of course. 🙂

https://1drv.ms/f/s!AoDi991gwI5uaoV3qNYic1k3cr4

[v-shex-msft · Accepted solution]

Hi @Psycho,

You can use measure to instead the calculated column Spalte, and it will works on slicer:

Measure:

Spalte(dymic) = SWITCH(MAX([Refer.Wert]), 1, [01 LUG.AktJ], 2,[02 LUG.AktJ], 3,[03 LUG.AktJ], 4,[04 LUG.AktJ]
               , 5, [05 LUG.AktJ], 6, [06 LUG.AktJ], 7, [07 LUG.AktJ], 8, [08 LUG.AktJ] 
               , 9, [09 LUG.AktJ], 10, [10 LUG.AktJ], 11, [11 LUG.AktJ], 12, [12 LUG.AktJ] 
               , 0)

Regards,

Xiaoxin Sheng

[Psycho]

Thank you so much. I learned a lot from you