cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Reversing my master tab

Hello, 

 

In Excel I have two tab, “Master” and “Red P”.  Initially, I needed use only “conformity toy” and “conformity CP” columns in Power BI. 

 

Now I need to show in a Histogram conformity of each CP, (I have 23 CP columns) by day. To have a good visual I « need » to reverse my 23 columns in rows and my date column in  columns (problem I have 8423 rows because date column is a datatime type). I would use SUMPRODUCT in excel (see "red P" tab). It means to create at least 366 columns (one by day) by year!!! I think it is a pity to use Power BI only to create visuals. I would like to do this table in Power BI and make it automatic because, of course, I will update my database “Master” with new data each day.  

 

My “Red P” tab show an example of “reversed matrix” in Excel when I only needed to show conformity in YTD and for the current month. It also show what I need as visual.     

 

Please, I just started using Power BI, could you give some ideas with good descriptions of steps to do it (in Power BI)? And the useful DAX or query M functions…  

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Frequent Visitor

Re: Reversing my master tab

@v-yuta-msft 

 

Thank you for your answer. 

 

I have posted my question (and explained it better) in the right place to do it 😅... I had my answer !!

 

https://community.powerbi.com/t5/Power-Query/Translate-SUMPRODUCT-using-a-Query-Power-BI/m-p/1140847...

 

BR

View solution in original post

5 REPLIES 5
Highlighted
Frequent Visitor

Re: Reversing my master tab

Here my excel file with master tab and Red P tab 

 

Test file 

 

 

Highlighted
Community Support
Community Support

Re: Reversing my master tab

@ItoDiaz ,

 

Have you tried "Pivot Column" in query editor?

1.PNG 

 

Regards,

Jimmy Tao

Highlighted
Frequent Visitor

Re: Reversing my master tab

@v-yuta-msft 

 

Yes, I have tried it at least four times, but PBI don't answer and I must stop process of the task. That is only the first question. 

 

I think I dind't clearly described my real question : how could I "Translate" this operation from excel in PBI? 

=100*(SUMPRODUCT((Mastertable[CP xx]=1)*(Mastertable[Date]=REDtable[Date]*1))/(SUMPRODUCT((Mastertable[CP xx]>=0)*(Mastertable[Date]=REDtable[Date]*1))"

 

Knowing I have 23 CP xx and I need to do the same operation for each one by day.  And I want to have my "CP xx" in rows.

 

ex : 

PCPDate 1Date 2Date 3....
CP_CONDUCT_AAresult aresult b.... 
CP_TEMP_AAresult xx........ 
CP_BRO_AA    

 

Highlighted
Community Support
Community Support

Re: Reversing my master tab

@ItoDiaz ,

 

You can use DAX fucntion SUMX() which is same with SUMPRODUCT() in excel. For more details, please refer to the doc:

https://docs.microsoft.com/en-us/dax/sumx-function-dax

 

Regards,

Jimmy Tao

Highlighted
Frequent Visitor

Re: Reversing my master tab

@v-yuta-msft 

 

Thank you for your answer. 

 

I have posted my question (and explained it better) in the right place to do it 😅... I had my answer !!

 

https://community.powerbi.com/t5/Power-Query/Translate-SUMPRODUCT-using-a-Query-Power-BI/m-p/1140847...

 

BR

View solution in original post

Helpful resources

Announcements
June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors