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!
I have a table with measure (from fact table) and 4 attributes from 4 different dimenion tables joined to this fact.
I have used Calculate + filter + all formula in the measure to get the necesasary filter context (e.g. all dates smaller then the one in slicer).
Next I'm trying to write a formula that witout modifying this filter context would let me sum by two of the dimensions, disregarding other two. And only for those dates that the first measure has filtered.
So for instance I have DATE / PRODUCT / STORE / EMPLOYEE / AMOUNT and I'm trying to write a formula for AMOUNTBYPRODUCTSTORE that would show on each row with PRODUCT + STORE combination the total for that combination in the table without impacting filter cotext on DATE given by first AMOUNT formula and disregarding EMPLOYEE column
Is this possible? Please suggest the approach? Thanks
I need some clarification terminology
without impact
disregarding
those terms can have multiple meanings.
I assume you are familiar with KEEPFILTERS and ALLEXCEPT concepts. Can you please describe for each of your scenarios which filters you want to enforce, which filters you want to keep, and which filters you want to remove?
Hi,
I'll try to explain again:
As a starting point I have a table with four dimensions and a measure
DATE / PRODUCT / STORE / EMPLOYEE / AMOUNT
In the Amount column I have a Calculate + filter + all formula for filtering all dates <= max (date). This then gives me all history in the table until the the DATE I chose in the slicer.
Let's say AMOUNT can also be negative - e.g. due to returns and return can have different EMPLOYEE and different DATE then the sale. So the end goal is to filter out sales and their returns in case on STORE and PRODUCT basis the sum is 0 - but for that I need a measure first.
So I'm trying to come up with formula to do what is done below:
The new measure should within the table sum by PRODUCT and STORE and then display the sum on each row.
DATE / PRODUCT / STORE / EMPLOYEE / AMOUNT / NEW MEASURE
1 / BAG / 1 / JOHN / 100 / 0
2 / PILLOW / 2 / JOHN / 100 / 150
3 / BAG / 1 / ANNA / -100 / 0
4 / BALL / 2 / JOHN / 200 / 200
5 / PILLOW / 2 / ANNA / 50 / 150
Is this clearer now? Thanks A LOT
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 |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
32 | |
30 | |
18 | |
18 |