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.
Hi
Is is possible to generate automatically in table in power query from a table created in power BI desktop with different calculated measures?
Thanks in advance
Mickael
Hi miguesnet,
I am not clear about your requirement, did you want to get measure in Power Query? The measure won't show in power query, they are different, one is M code(which will show the transformation of table) , one is DAX (create aggregration of table).
If this is not what you want, please corrrect me and explain your requirement in details.
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Zoe,
I will try to detail what i would like to do.
I want to ultimately create a waterfall chart.
My datas are at a daily level.
I want to aggregatte data at a monthly level taking all the lines for the amount ( criteria 1 and 0) and only lines with criteria 1 for KG
I need at this level to add 3 calculated columns : Effect 1 , Effect 2 et Effect 3 ( based on N and N-1 values)
I want then to be able to depivot column to create automatically a third table that i will use to do waterfall charts.
Hope this is clear.
Thanks in advance for your help.
Best Regards
Mickael
Hi miguesnet,
I couldn't understand your logic of calculated columns(effect1, effect2, effect3 which based on N and N-1 values), so if possible could you please explain to me in details?
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi
N = This year
N-1 = Last year.
Calculated colums will be based on the following formulas but this is not what was blocking me.
Effect 1 = (Cost.kg N - Cost .kg N-1)* Kg N
Effect 2 = (Kg N - KG N-1)* (Cost.kg N - Avge Cost . Kg N-1)
Effect 3 = (Kg N - KG N-1)* Avge Cost . Kg N-1
Cost.kg = Amount / kg
I was struggling to go through the differents steps
1. Agregation of datas
2. Calculation of effect
3. Depivoting of columns
4. Waterfall charts
Hope this makes sense
Regards
Mickael
Hi miguesnet,
I want to know whether Cost.kg = Amount / kg need to exclude criteria=0's record? You could refer to my sample to see whether it work or not. If this is not what you want, please inform me more deatiled information(such as your data sample and your expected output).
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
Thanks for your answer. That is where i managed to go so far. Utimately, my goal is to present datas uner a waterfall chart . I wanted to upload a pbix file but unfortunately i am not able.
In order to demonstrate what i want to achieve :
- From the second table with effect calculation that you have done : i extracted it into Excel and uploaded it in power query ( not really efficient !
- I then created a second table which makes reference to the first one and then I depivoted column effect 1 , 2 and 3
- which then allowed me to do the waterfall chart with the effect analysis
I would like to carry out all those steps automatically.
Thanks
Mickael
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 |
---|---|
102 | |
53 | |
21 | |
13 | |
11 |