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! Is there a way in Power BI to ensure that as I add new items to my file that it dynamically adds the columns to my calculation? Right now my variance formula looks like this: Var_Planilla_Integral_2 = SUM('Planilla Integral (2)'[2016-17])-SUM('Planilla Integral (2)'[2017-18]) .... and as time progresses I will be looking at this over multiple categories and was hoping that somehow I could get Power BI to grab all of those fields? Or do I need to add them manually. The file I am referencing is below.
https://drive.google.com/open?id=1bljr8wBK7nH8tOKDSNQQOf1xcdcJQBMJ - Power BI File
https://drive.google.com/open?id=10erRiMIuK_m-9kXkNq5L7H6_ggvqRAEB - Expected Results
Thanks!
Hello! Is there a way in Power BI to ensure that as I add new items to my file that it dynamically adds the columns to my calculation? Right now my variance formula looks like this: Var_Planilla_Integral_2 = SUM('Planilla Integral (2)'[2016-17])-SUM('Planilla Integral (2)'[2017-18]) .... and as time progresses I will be looking at this over multiple categories and was hoping that somehow I could get Power BI to grab all of those fields? Or do I need to add them manually. The file I am referencing is below.
https://drive.google.com/open?id=1bljr8wBK7nH8tOKDSNQQOf1xcdcJQBMJ - Power BI File
https://drive.google.com/open?id=10erRiMIuK_m-9kXkNq5L7H6_ggvqRAEB - Expected Results
Thanks!
Hi,
I'd like ot help but language is proving to be a barrier. Can the column titles of your Planilla Integral dataset be in English please. Also, i will need that source table to work with. Share that dataset with column titles in English, if possible. Also, i see that in the Query Editor, you have carried out some transformations.
It will be ideal if you can just share the dataset (input) and show me the result you are expecting (output).
Is it possible to do something like create a PCTEMP sequence table with one column being the different temporadas and the other being the sequence, and then using LOOKUP with an offset for the sequence? So compare the sum of PCAA for the current temporada with the sum of the PCAA for the temporada sequence -1?
see the link .pbix file https://drive.google.com/open?id=1SgodCEaH9NwCJ3VjNEaybPJEi-1YsPzV
This might be a bit clunky, but I think you get the idea.
There is a sequencing table, which is referenced by a calculated sequence column in your Planilla Integral
PCTEMP_Seq = LOOKUPVALUE(tblPCTempSeq[PCTEMP_Seq],tblPCTempSeq[PCTEMP],'Planilla Integral'[PCTEMP])
That's referenced to get the corresponding temporada for the previous period
PCTEMP_Prev = LOOKUPVALUE(tblPCTempSeq[PCTEMP],tblPCTempSeq[PCTEMP_Seq],'Planilla Integral'[PCTEMP_Seq]-1)
Then you can get the total PCAA (or whatever column you care about) for the current and previous period with
PCAA Current = CALCULATE(sum('Planilla Integral'[PCAA]),FILTER(ALL('Planilla Integral'), 'Planilla Integral'[PCTEMP]=EARLIER('Planilla Integral'[PCTEMP])&& 'Planilla Integral'[PCDL01]=EARLIER('Planilla Integral'[PCDL01])))
and
PCAA Prev = CALCULATE(sum('Planilla Integral'[PCAA]),FILTER(ALL('Planilla Integral'), 'Planilla Integral'[PCTEMP]=EARLIER('Planilla Integral'[PCTEMP_Prev])&& 'Planilla Integral'[PCDL01]=EARLIER('Planilla Integral'[PCDL01])))
Then one final measure to compare
PCAAVar = AVERAGE('Planilla Integral'[PCAA Current])-AVERAGE('Planilla Integral'[PCAA Prev])
This probably isn't as flexibile or efficient as you want, but I thnk it's the right direction to go in. I'll stay subscribed to see what if anything better anyone else can come up with.
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 |
---|---|
115 | |
100 | |
88 | |
69 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |