Reversing my master tab

Question

Hello,&nbsp;&nbsp;In Excel I have two tab, “Master” and “Red P”.&nbsp; Initially, I needed use only “conformity toy” and “conformity CP” columns in Power BI.&nbsp;&nbsp;Now I need to show in a Histogram conformity of each CP, (I have 23 CP columns) by day. To have a good visual&nbsp;I « need » to reverse my 23 columns in rows and my date column in&nbsp; columns&nbsp;(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. &nbsp;&nbsp;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. &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;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… &nbsp;

itodiaz · Answer

Here my excel file with master tab and Red P tab&nbsp;&nbsp;Test file&nbsp;&nbsp;&nbsp;

v-yuta-msft · Answer

ItoDiaz&nbsp;,
&nbsp;
Have you tried "Pivot Column" in query editor?
&nbsp;
&nbsp;
Regards,
Jimmy Tao

itodiaz · Answer

v-yuta-msft&nbsp;&nbsp;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.&nbsp;&nbsp;I think I dind't clearly described my real question : how could I "Translate" this operation from excel in PBI?&nbsp;=100*(SUMPRODUCT((Mastertable[CP xx]=1)*(Mastertable[Date]=REDtable[Date]*1))/(SUMPRODUCT((Mastertable[CP xx]&gt;=0)*(Mastertable[Date]=REDtable[Date]*1))"&nbsp;Knowing I have 23 CP xx and I need to do the same operation for each one by day.&nbsp; And I want to have my "CP xx" in rows.&nbsp;ex :&nbsp;PCPDate 1Date 2Date 3....CP_CONDUCT_AAresult aresult b....&nbsp;CP_TEMP_AAresult xx........&nbsp;CP_BRO_AA&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

v-yuta-msft · Answer

ItoDiaz&nbsp;,
&nbsp;
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
&nbsp;
Regards,
Jimmy Tao

itodiaz · Answer

v-yuta-msft&nbsp;&nbsp;Thank you for your answer.&nbsp;&nbsp;I have posted my question (and explained it better) in the right place to do it&nbsp;😅... I had my answer !!&nbsp;https://community.powerbi.com/t5/Power-Query/Translate-SUMPRODUCT-using-a-Query-Power-BI/m-p/1140847#M37244&nbsp;BR

Forum Discussion

Reversing my master tab

5 Replies

Related content

Formatted Table Reversing Causes Errors

Master Data Service in Fabric ?

Reverse filtering

Master Models

Reverse Filter?

PCP	Date 1	Date 2	Date 3	....
CP_CONDUCT_AA	result a	result b	....
CP_TEMP_AA	result xx	....	....
CP_BRO_AA