Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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…
@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
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 !!
BR
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 :
PCP | Date 1 | Date 2 | Date 3 | .... |
CP_CONDUCT_AA | result a | result b | .... | |
CP_TEMP_AA | result xx | .... | .... | |
CP_BRO_AA |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |