Reversing my master tab

ItoDiaz · ‎05-29-2020

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…

v-yuta-msft · ‎06-04-2020

@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

ItoDiaz · ‎06-05-2020

@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

ItoDiaz · ‎06-01-2020

@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 :