Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I am new to Power BI. Learning on My own.I am attaching an excel file with some data inputs .
The columns highlighted in BOLD / Yellow are the new calculated colums I want to create in table or query .(3 columns)
Explanation for Calculated columns as below.
Monthly Consumption - Sum Mothly values by each Month for each country
Waste % - Waste divided by monthly consumption .
Cumulative Waste % - Cumulative waste for each month and Country.
Please suggest ways to resolve.
Regards
Joshi
Solved! Go to Solution.
This is generally not how you should be approaching this. Have a read of my article here https://exceleratorbi.com.au/calculated-columns-vs-measures-dax/
Hi,
Pasting the excel file.
Let me know how to go about it
Thanks
Regards
Joshi
Date | Country | Material | Wate | Consumption | Monthly Consumption | Monthly Waste% | Cumulative Waste% |
1/1/2019 | Brazil | A | 200 | 1200 | 3200 | 6% | 6% |
1/1/2019 | Brazil | B | 100 | 1000 | 3200 | 3% | 9% |
1/1/2019 | Brazil | C | 100 | 1000 | 3200 | 3% | 13% |
1/1/2019 | China | A | 200 | 1100 | 3500 | 6% | 6% |
1/1/2019 | China | B | 100 | 1000 | 3500 | 3% | 9% |
1/1/2019 | China | C | 200 | 1400 | 3500 | 6% | 14% |
1/1/2019 | India | A | 100 | 1000 | 3200 | 3% | 3% |
1/1/2019 | India | B | 200 | 1200 | 3200 | 6% | 9% |
1/1/2019 | India | C | 200 | 1000 | 3200 | 6% | 16% |
2/1/2019 | Brazil | A | 300 | 1400 | 3900 | 8% | 8% |
2/1/2019 | Brazil | B | 200 | 1200 | 3900 | 5% | 13% |
2/1/2019 | Brazil | C | 100 | 1300 | 3900 | 3% | 15% |
2/1/2019 | China | A | 200 | 1300 | 3900 | 5% | 5% |
2/1/2019 | China | B | 100 | 1500 | 3900 | 3% | 8% |
2/1/2019 | China | C | 200 | 1100 | 3900 | 5% | 13% |
2/1/2019 | India | A | 200 | 1500 | 3900 | 5% | 5% |
2/1/2019 | India | B | 200 | 1200 | 3900 | 5% | 10% |
2/1/2019 | India | C | 100 | 1200 | 3900 | 3% | 13% |
3/1/2019 | Brazil | A | 200 | 1300 | 3900 | 5% | 5% |
3/1/2019 | Brazil | B | 200 | 1200 | 3900 | 5% | 10% |
3/1/2019 | Brazil | C | 100 | 1400 | 3900 | 3% | 13% |
3/1/2019 | China | A | 200 | 1400 | 3700 | 5% | 5% |
3/1/2019 | China | B | 300 | 1100 | 3700 | 8% | 14% |
3/1/2019 | China | C | 200 | 1200 | 3700 | 5% | 19% |
3/1/2019 | India | A | 300 | 1000 | 3300 | 9% | 9% |
3/1/2019 | India | B | 200 | 1200 | 3300 | 6% | 15% |
3/1/2019 | India | C | 100 | 1100 | 3300 | 3% | 18% |
Hi,
Writing measures would be better. Share the link from where i can download your PBI file. Alternatively paste data here such that i can paste it in an Excel workbook.
Hi,
Pasting the excel file.
Let me know how to go about it
Thanks
Regards
Joshi
Date | Country | Material | Wate | Consumption | Monthly Consumption | Monthly Waste% | Cumulative Waste% |
1/1/2019 | Brazil | A | 200 | 1200 | 3200 | 6% | 6% |
1/1/2019 | Brazil | B | 100 | 1000 | 3200 | 3% | 9% |
1/1/2019 | Brazil | C | 100 | 1000 | 3200 | 3% | 13% |
1/1/2019 | China | A | 200 | 1100 | 3500 | 6% | 6% |
1/1/2019 | China | B | 100 | 1000 | 3500 | 3% | 9% |
1/1/2019 | China | C | 200 | 1400 | 3500 | 6% | 14% |
1/1/2019 | India | A | 100 | 1000 | 3200 | 3% | 3% |
1/1/2019 | India | B | 200 | 1200 | 3200 | 6% | 9% |
1/1/2019 | India | C | 200 | 1000 | 3200 | 6% | 16% |
2/1/2019 | Brazil | A | 300 | 1400 | 3900 | 8% | 8% |
2/1/2019 | Brazil | B | 200 | 1200 | 3900 | 5% | 13% |
2/1/2019 | Brazil | C | 100 | 1300 | 3900 | 3% | 15% |
2/1/2019 | China | A | 200 | 1300 | 3900 | 5% | 5% |
2/1/2019 | China | B | 100 | 1500 | 3900 | 3% | 8% |
2/1/2019 | China | C | 200 | 1100 | 3900 | 5% | 13% |
2/1/2019 | India | A | 200 | 1500 | 3900 | 5% | 5% |
2/1/2019 | India | B | 200 | 1200 | 3900 | 5% | 10% |
2/1/2019 | India | C | 100 | 1200 | 3900 | 3% | 13% |
3/1/2019 | Brazil | A | 200 | 1300 | 3900 | 5% | 5% |
3/1/2019 | Brazil | B | 200 | 1200 | 3900 | 5% | 10% |
3/1/2019 | Brazil | C | 100 | 1400 | 3900 | 3% | 13% |
3/1/2019 | China | A | 200 | 1400 | 3700 | 5% | 5% |
3/1/2019 | China | B | 300 | 1100 | 3700 | 8% | 14% |
3/1/2019 | China | C | 200 | 1200 | 3700 | 5% | 19% |
3/1/2019 | India | A | 300 | 1000 | 3300 | 9% | 9% |
3/1/2019 | India | B | 200 | 1200 | 3300 | 6% | 15% |
3/1/2019 | India | C | 100 | 1100 | 3300 | 3% | 18% |
Hi,
Sorry for the late respone.
Solution Understood.
Wanted to check How can i get a column for YTD consumption per Country
Thanks
Joshi
There are functions for that, but you need a calendar table. Read about it here https://exceleratorbi.com.au/dax-time-intelligence-beginners/
Hi,
You may download my PBI file from here. I cannot figure out how to compute the last measure for now.
Hope this helps.
Here is the formula for cumulative waste %
Cumulative Monthly Waste % = DIVIDE ( CALCULATE ( SUM ( Data[Wate ] ), FILTER ( ALL ( Data[Material] ), Data[Material] <= MAX ( Data[Material] ) ) ), [Monthly Consumption] )
This is generally not how you should be approaching this. Have a read of my article here https://exceleratorbi.com.au/calculated-columns-vs-measures-dax/
Thanks. Solution understood.
Sorry for the late response.
Thanks
Joshi
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |