Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Community, hoping you can help me with this task.
I have this data table tblForecast:
Category__________Month_____________Cost
Cat1________________Sep_______________100
Cat1________________Dec_______________104
Cat6________________Oct_______________102
Cat6________________May_______________10
Cat2________________Jan_______________120
Cat3________________Apr_______________900
.... and so on. Lots of combinations
And a look-up table of the months (connected by 1 to many to the tblForecast):
Month_______ForecastOrActuals
Sep__________________A
Oct__________________A
Nov__________________A
Dec__________________A
Jan__________________F
Feb__________________F
Mar__________________F
Apr__________________F
May_________________F
Jun__________________F
Jul__________________F
Aug__________________F
So, I need to create a measure that do the following:
"Sum the Cost column in tblForecast but only for those months that have an "F" in the column "ForecastOrActuals" from the table month"
Please let me know if you need further details. As always, thank you very much in advance!
Kind regards,
Andy.-
Solved! Go to Solution.
I believe what you want is something along the lines of:
Measure = CALCULATE(SUM(TblForecast[Cost]),FILTER(RELATEDTABLE(Months),[ForecastOrActuals]="F"))
Let me know if that works. If not, I'll try to recreate your data and test.
I believe what you want is something along the lines of:
Measure = CALCULATE(SUM(TblForecast[Cost]),FILTER(RELATEDTABLE(Months),[ForecastOrActuals]="F"))
Let me know if that works. If not, I'll try to recreate your data and test.
Absolutely amazing @Greg_Deckler! Worked perfectly. And it will be updated automatically because the column ForecastOrActuals has a DAX formula that put A or F based on other tables conditions. I created a post that is related to this one, maybe you can take a look at it:
https://community.powerbi.com/t5/Desktop/Execute-a-mesure-based-on-a-condition/m-p/345453
I need to start using RELATED and RELATEDTABLE... Still newbie into this world.
Thank you very much!
Regards,
Hah! My DAX must be getting better! Usually when I try to write a formula from memory instead of recreating the scenario I make some stupid syntax error!! 🙂 I'll take a look at the link.
Is there a relationship between the two tables?
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |