cancel
Showing results for
Did you mean:
Frequent Visitor

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

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

3 ACCEPTED SOLUTIONS
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Frequent Visitor

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.  😞

Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
10 REPLIES 10
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Frequent Visitor

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.

Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Frequent Visitor

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.  😞

Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Frequent Visitor

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

Community Support

Hi @Psycho,

Can you share it to 1drive?

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Frequent Visitor
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Frequent Visitor

Thank you so much. I learned a lot from you

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.