March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hello everyone,
I have a table with 5 columns [Date, ID, ZipCode, Numeric, Condition]
Date | ID | ZipCode | Number | Condition |
27/03/2020 | A | MTS | 117525 | Yes |
27/03/2020 | B | MTS | 117525 | Yes |
19/03/2020 | C | MTS | 117525 | Yes |
25/02/2020 | D | MTS | 0 | No |
20/01/2020 | E | FCM | 120368 | Yes |
13/03/2020 | F | FCM | 120368 | Yes |
17/03/2020 | G | FCM | 0 | No |
05/04/2020 | H | MTS | 247831 | Yes |
31/03/2020 | I | MTS | 0 | No |
08/04/2020 | J | MTS | 247831 | Yes |
The goal is to divide the Number with the total count of "Yes".
How do i lock that count for each row?
Edit: I add another ZipCode to clarify the goal
Solved! Go to Solution.
@Anonymous , New column
new column =divide( [Number], countx(filter(Table, [zip code] =earlier([Zip Code]) && [Condition]= "Yes" ), [ID] ) )
@Anonymous
Use this for a calculated column
Column1 =
VAR ZipCodeTable =
CALCULATETABLE ( Table, ALLEXCEPT ( Table, Table[ZipCode] ) )
VAR YesTable =
FILTER ( ZipCodeTable, Table[Condition] = "Yes" )
RETURN
IF (
Table[Condition],
0,
DIVIDE ( SUMX ( YesTable, Table[Number] ), COUNTROWS ( YesTable ) )
)
Hi @Anonymous
I guess you are using table visual by ZipCode. Then you can use
Measure1 =
DIVIDE (
SUM ( Table[Number] ),
COUNTROWS ( FILTER ( Table, Table[Condition] = "Yes" ) )
)
@Anonymous , New column
new column =divide( [Number], countx(filter(Table, [zip code] =earlier([Zip Code]) && [Condition]= "Yes" ), [ID] ) )
When the condition is "No" the new column should be 0.
The output should be 39175 | 39175 | 39175 | 0 (these are rows).
The zipcode can change along the table
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
21 | |
14 | |
11 | |
8 | |
5 |
User | Count |
---|---|
26 | |
21 | |
20 | |
14 | |
10 |