cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Psycho
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

Hi @Psycho,

 

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

 

Sample:

 

Capture3.PNG

 

Use duplicate function to backup this table:

 

Capture4.PNG

 

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

 

Capture5.PNG

 

Result:

 

Capture6.PNG

 

After above steps, you can continue the unfinished work. Smiley Happy

 

Regards,

Xiaoxin Sheng

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

View solution in original post

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 ^^

Unbenannt.png

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

View solution in original post

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)

 

Capture3.PNGCapture4.PNGCapture5.PNGCapture6.PNG

 

Regards,

Xiaoxin Sheng

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

View solution in original post

10 REPLIES 10
v-shex-msft
Community Support
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:

 

Capture.PNG

 

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]))

 

Capture2.PNG

 

Create visual:

 

Capture3.PNG

 

>>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)))

 

Capture4.PNG

 

Regards,

Xiaoxin Sheng

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

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.

Hi @Psycho,

 

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

 

Sample:

 

Capture3.PNG

 

Use duplicate function to backup this table:

 

Capture4.PNG

 

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

 

Capture5.PNG

 

Result:

 

Capture6.PNG

 

After above steps, you can continue the unfinished work. Smiley Happy

 

Regards,

Xiaoxin Sheng

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

View solution in original post

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 ^^

Unbenannt.png

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

View solution in original post

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.

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

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.

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)

 

Capture3.PNGCapture4.PNGCapture5.PNGCapture6.PNG

 

Regards,

Xiaoxin Sheng

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

View solution in original post

Thank you so much. I learned a lot from you Smiley Happy

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

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.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors