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 am creating the below measure
M_Freight Variance = CALCULATE(sum(Book2[Freight]),Book2[PC]="PC04 FY18")-CALCULATE(sum(Book2[Freight]),Book2[PC]="PC3 FY 18 REV")
In this the values on which data is being filter are coming from excel file/ data fields, these values need to be dynamic as one excel file will contain ony two such values whenever a new excel file is loaded these filters need to change according to data and there are multiple values which are used, so can't predfine them.
How to do this ?
Solved! Go to Solution.
I am using a measure where i am calcuting total sales and filtering it on a particular column.
M_Freight Variance = CALCULATE(sum(Book2[Freight]),Book2[PC]="PC04 FY18")-CALCULATE(sum(Book2[Freight]),Book2[PC]="PC3 FY 18 REV")
these values keep on changing pc3,pc4,pc5 etc and in one file on which the report is built will have only two such values.
I want to pick these values from excel column and filter on the bases of them.
How to do so ?
Max will work in case of two pc's only, i have pc from 1 to 8 with financial year changing, so no to pc will be same.
In this scenario, you should have a FY column and a PC column in your table. Both columns should be numeric so that we can get the "Previous" one in FILTER().
Then you can write a measure like below:
M_Freight Variance = CALCULATE ( SUM ( Book2[Freight] ) ) - CALCULATE ( SUM ( Book2[Freight] ), FILTER ( ALL ( Book2 ), Book2[PC] = MAX ( Book2[PC] ) - 1 && Book2[FY] = MAX ( Book2[FY] ) ) )
Regards,
I am using a measure where i am calcuting total sales and filtering it on a particular column.
M_Freight Variance = CALCULATE(sum(Book2[Freight]),Book2[PC]="PC04 FY18")-CALCULATE(sum(Book2[Freight]),Book2[PC]="PC3 FY 18 REV")
these values keep on changing pc3,pc4,pc5 etc and in one file on which the report is built will have only two such values.
I want to pick these values from excel column and filter on the bases of them.
How to do so ?
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 |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |