Piechart diagram based on statistics (percentage calculation)
Dear all, I’m a Data Analysis course student, trying to make a Piechart diagram to represent a sample’s educational background (60.000 people in the sample). There are 5 groups of educational institutions (column [edulvlb] of my table ‘education’: basic education (codes 000,113,129), school diploma holders (codes 212 || 213 || 221 || 222 || 223 || 229 || 311 || 312 || 313 || 321 || 322 || 323), college diploma holders (codes 412 || 413 || 421 || 422 || 423 || 510 || 520), university diploma holders (codes 610 || 620 || 710 || 720) and PhD degree holders (code 800). So I need to count the codes per category (for example, how many times 000,113, 129 appear in column [edulvlb]) and then divide it by total amount of unfiltered rows (about 60.000 rows) of my entire datascheet. Therefore the choice of || which means OR in data selection… So I have divided this process in 2 steps: 1. Basis = CALCULATE (COUNTROWS(education),FILTER ('education',education[edulvlb] = 000 || 113 ||129)) School = CALCULATE (COUNTROWS(education),FILTER ('education',education[edulvlb] = 212 || 213 || 221 || 222 || 223 || 229 || 311 || 312 || 313 || 321 || 322 || 323)) College = CALCULATE (COUNTROWS(education),FILTER ('education',education[edulvlb] = 412 || 413 || 421 || 422 || 423 || 510 || 520)) Uni = CALCULATE (COUNTROWS(education),FILTER ('education',education[edulvlb] = 610 || 620 || 710 || 720)) PhD = CALCULATE (COUNTROWS(education),FILTER ('education',education[edulvlb] = 800)) 2. PercentageBasis = DIVIDE([Basis], Calculate([Basis], ALL('education'))) PercentageSchool = DIVIDE([School], Calculate([School], ALL('education'))) PercentageCollege = DIVIDE([College], Calculate([College], ALL('education'))) PercentageUni = DIVIDE([Uni], Calculate([Uni], ALL('education'))) PercentagePhD = DIVIDE([PhD], Calculate([PhD], ALL('education')) However, the piechart shows 5 segments of totally similar size 20%.. what have I done wrong here..maybe you could advise ?? Looking forward to hearing from you, thank you again for your help !! Best regards, Katia
Standard Deviation from 4 measured columns.... DARN!
Hi all! I've researched so many sites (and previous answers) concerning computing standard deviation in PowerBI. I never knew it would be so difficult for me to do since Excel makes it SIMPLE! I have a matrix table that I want to resemble an excel table. The data shown are all aggregate values compiled from individual records (with some duplicates). All columns are measures. It is important for the pivot to remain and that I only calculate the current month for each year. For an example, I want to calculate March for the current year and March for the previous 5 years (2022 - 2017). While the STDEVX.P function is fairly simple, I need it to only reference my previous 5 year columns (2017-2021). When I use the function, it references EVERYTHING since I don't have a DAX formula to add in the caveats. If my vision is possible, please help me out or let me know of alternatives. I will give kudos and I don't mind reading recommendations!LINEAR REGRESSION - X-AXIS IN TEXT FORMAT
Hello I am working with a data sample that contains numeric values on "y" axis (pH results) and text values on "x" axis (sample/batch code) and need to build a linear regression analysis. So far, I found a website "IterationInsights" that published an article "How to do simple linear regression in Power BI", which really helped me to analyse using date, but not sample code (since it is a text). I need to evaluate the trend between 2 samples, independent of when they were collected. Note that if x is a Date, we have: y = 0.0023x - 99.065, r² = 0.2566 But if x is the sample, we have: y = 0.0297x + 5.5252, r² = 0.3024 How am I able to build a variable table that converts the sample code to a numeric sequence (A1AA/22 = 1, A2AA/22 = 2, ...., A21AA/22 = 21)? Besides, it's possible to filter this table? Ex: analyse A12AA/22 to A20AA/22 (interval which would automatically be converted to the numbers: A12AA/22 = 1, A13AA/22 = 2, ...., A20AA/22 = 9, always starting with 1). Since slope formula is: [n(Σxy) - (Σx)(Σy)] / [n(Σx²) - (Σx)²], x should be refeered as a sequence of natural numbers representing the samples. n is the distinct count of the samples. I suppose that I should build a data var table to convert sample code to a number, and use All selected function to filter the samples I'm working with, but I'm really having a hard time to combine these functions and obtain a functional linear regression equation. I exemplified the solpe formula because I think the same solution would be applied to intercep, r², etc. For slope, I used the formula: --------------------------------------------------- SLOPE = var data = SELECTCOLUMNS(ALLSELECTED(DATA), "x_values",DATA[DATE], "y_values",DATA[RESULTS] ) --Variables to Solve for: var y_sum = SUMX(data,[y_values]) var x_sum = SUMX(data,[x_values]) var x2 = SUMX(data,[x_values]^2) var xy = SUMX(data,[x_values]*[y_values]) var row_count = COUNTROWS(data) --Solve for formula var Slope = ((row_count * xy) - (x_sum * y_sum))/((row_count*x2)-((x_sum)^2)) return Slope ----------------------------------------------------- Anyone could help me?
