Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi. My table "data" in Power BI captures data for all 12 months for budget and 9 months of actual sales. My Power BI report has a page filter set for "month". I would like to create a measure in Power BI desktop which can generate the total year budget sales without having to remove the page filter.
This is what I had created for the measure:
Measure = calculate(sum(data[Sales]),data[version]="Budget",all(data[month]))
The result however only captures 9 months' of budget sales. Pls advise how I should amend the DAX formula. Thank you
Solved! Go to Solution.
Hi @bitbit,
You can create a measure as below.
total year budget sales = CALCULATE(SUM(data[Sales]),FILTER(ALLEXCEPT(data,data[Country]),data[version]="Budget"))
For more details, please check the pbix as attached.
Regards,
Frank
Hi @bitbit,
You can create a measure as below.
total year budget sales = CALCULATE(SUM(data[Sales]),FILTER(ALLEXCEPT(data,data[Country]),data[version]="Budget"))
For more details, please check the pbix as attached.
Regards,
Frank
Hi Frank. Solution works, many thanks!
Hi @bitbit,
Please try to use this formual to create a measure. If it doesn't meet your requirement, Kindly share your pbix to me.
Measure = calculate(sum(data[Sales]),Filter(all(data),data[version]="Budget"))
Regards,
Frank
Hi. Pls advise how I can obtain total year budget sales for each country in my sample pbix report (below url) without having to adjust the page filter.
You basically have to clear all filters which are dependant on Sales, not just Date.
Try this:
Measure = calculate(sum(data[Sales]),data[version]="Budget",all(data))
Tell us if works for you.
Hi, Miltinho. My table includes data breakdown into various sales area, by changing the formula, I can no longer get the total for each sales area which I need for other BI charts.
Hi bitbit,
It's better if you include a file with sample data with what you have to help you in a right way.
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |