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.
Goodday,
I need an adjustment of a working formula
HI, @RvdHeijden
You could use Calculate Function to evaluate an expression in a context that is modified by the specified filters.
like:
% Actief Provider A = Divide(Calculate(SUM('Table'[Aantal Provider A]), filter('Table', 'Table' [Carrier]="Carrier")),(sum('Table'[Aantal Actief])))
For example:
result = DIVIDE(CALCULATE( SUM('Table'[Qty]),FILTER('Table','Table'[Carier]="Carier A")),SUM('Table'[Qty]))
Best Regards,
Lin
Lin, that might be a step in the right direction but we are not quite there yet.
It first has to filter in 2 different collumns in another table called 'EFOS' so basically IF a records is from Provider A and Carrier A then it should Add them up first and then devide it by the total amount of active users.
Something like this:
hi, @RvdHeijden
For two or more conditionals in the formula, you should use “&&” or AND() in it.
For example
% Actief Provider A =
DIVIDE (
CALCULATE (
SUM ( 'Table'[Aantal Actief] ),
FILTER (
'EFOS',
'EFOS'[Carrier] = "Carrier A"
&& 'EFOS'[provider] = "Provider A"
)
),
SUM ( 'Table'[Aantal Actief] )
)
or
% Actief Provider A =
DIVIDE (
CALCULATE (
SUM ( 'Table'[Aantal Actief] ),
FILTER (
'EFOS',
AND ( 'EFOS'[Carrier] = "Carrier A", 'EFOS'[provider] = "Provider A" )
)
),
SUM ( 'Table'[Aantal Actief] )
)
By the way, EFOS'[provider]= Provider A,Provider A is a text, and should be "Provider A"
Best Regards,
Lin
In both examples the returned outcome is 100% and that is not possible because this provider has 2 carriers so it shouldn't be 100%
hi, @RvdHeijden
Please check:
1. if "Table" has created a relationship with "EFOS"
2. Use ALL/ ALLEXCEPT/ ALLSELECTED in your formula like below:
(here is a blog for you as a reference:
% Actief Provider A =
DIVIDE (
CALCULATE (
SUM ( 'Table'[Aantal Actief] ),
FILTER (
'EFOS',
AND ( 'EFOS'[Carrier] = "Carrier A", 'EFOS'[provider] = "Provider A" )
)
),
CALCULATE (
SUM ( 'Table'[Aantal Actief] ), ALL('EFOS'[Carrier], 'EFOS'[provider]))
)
If not your case, please share a sample pbix file or some data sample and expected output. Do mask sensitive data before uploading.
Best Regards,
Lin
Proud to be a Super User!
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 |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |