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 Folks,
I normally use Tableau and have recently started using Power BI, so don't know how to implement the desired functionality. I want to find percent change relative to the first year. For example, in my data source the first year is 1947 so I want to find percent change of each year wrto this year. In Tableau, I can do this with few clicks but as I am new to Power BI so struggling a bit. I created a formula but it is not working.
Please someone help me. If required, I am ready to send my .pbix file.
Regards,
Tushar
Solved! Go to Solution.
HI @tmore,
I can't find the specific column which you used in your formula.
If you means get the result of 'each year' divide 'all year'(per ethnicity), you can refer to below formula.
Measure formulas:
War percent = CALCULATE ( SUM ( Sheet2[% of WAR] ), VALUES ( Sheet2[Ethnicity] ) ,VALUES(Sheet2[Year])) / CALCULATE ( SUM ( Sheet2[% of WAR] ), ALL ( Sheet2 ), VALUES ( Sheet2[Ethnicity] ) ) Players percent = CALCULATE ( SUM ( Sheet2[% of Players] ), VALUES ( Sheet2[Ethnicity] ) ,VALUES(Sheet2[Year])) / CALCULATE ( SUM ( Sheet2[% of Players] ), ALL ( Sheet2 ), VALUES ( Sheet2[Ethnicity] ) )
Regards,
Xiaoxin Sheng
HI @tmore,
It seems like lookupvalue function can find out multiple correspond records, current power bi not support to direct calculation on multiple records.
Please share some sample data or pbix file to help us modify on your formula.
Regards,
Xiaoxin Sheng
Hi
I am facing the similar error in the expression
Could you please help me
LOOKUPVALUE(psc_historical[ADM.stage],psc_historical[PBM.pincode],psc_historical[PBM.pincode],psc_historical[scorecard_month_no],if(month(psc_historical[scorecard_date]) = 1,12,month(psc_historical[scorecard_date])-1))
Hi Xiaoxin,
Thanks for the prompt reply. I don't how to attach file in the thread. However, I've uploaded the excel file on my dropbox and sharing link with you so that you can download.
https://www.dropbox.com/s/ich5eopzldpmq50/MLB%20Ethnicity%201947-2016.xlsx?dl=0
Please show me the way to achive the desired functionality.
Let me know if you need more info regarding my problem.
Regards,
Tushar
HI @tmore,
I can't find the specific column which you used in your formula.
If you means get the result of 'each year' divide 'all year'(per ethnicity), you can refer to below formula.
Measure formulas:
War percent = CALCULATE ( SUM ( Sheet2[% of WAR] ), VALUES ( Sheet2[Ethnicity] ) ,VALUES(Sheet2[Year])) / CALCULATE ( SUM ( Sheet2[% of WAR] ), ALL ( Sheet2 ), VALUES ( Sheet2[Ethnicity] ) ) Players percent = CALCULATE ( SUM ( Sheet2[% of Players] ), VALUES ( Sheet2[Ethnicity] ) ,VALUES(Sheet2[Year])) / CALCULATE ( SUM ( Sheet2[% of Players] ), ALL ( Sheet2 ), VALUES ( Sheet2[Ethnicity] ) )
Regards,
Xiaoxin Sheng
Thanks for the answer. I think we are very close. Actually, with the data set I want to find percent change of each year from the starting year (1947) for each ethnicity. Here is an image what I am looking for. I made this using Tableau and want to replicate using Power BI.
Any thoughts how to achieve this?
Regards,
Tushar
HI @tmore,
You can create line chart with year to axis, ethnicity to legend, measure to value field to create a similar graph.
Notice: current line chart not support use multiple value and legend at same time, if you enable legend field, value field can only use one column/measure.
Regards,
Xiaoxin Sheng
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |