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.
I have the following table:
Note: I have a tran date that I did not inlcude below. It is related to a calendar table via tran date.
Cls Date | Key 1 | Key 2 | Cat | Status | Amount |
A1 | B1 | A | open | 2 | |
Jan 1,2020 | A1 | B2 | A | closed | 10 |
Jan 1,2020 | A1 | B3 | A | closed | 20 |
Jan 1,2020 | A2 | C1 | A | closed | 5 |
Jan 3,2020 | A2 | C2 | A | closed | -5 |
Jan 1,2020 | A3 | D1 | B | closed | 0 |
Jan 2,2020 | A4 | E1 | A | closed | 50 |
Jan 2,2020 | A5 | E2 | A | closed | -50 |
A5 | E3 | B | open | 30 | |
Jan 1,2020 | A6 | F1 | A | closed | 40 |
Jan 2,2020 | A6 | F2 | B | closed | -40 |
I need to crate an average that does this:
For each cls dte month do the following
Cls Date Month | Count of Distinct Key 1 where Total Amount <> 0 | Sum of Amount | Note |
Jan, 2020 | 1 | 10+20=30 | Counted A1 Not counted A2 since 5-5=0, A3 since 0=0, A4, A5 since 50-50=0, A6 since 40-40=0 |
Average 30/1=30
Note 2 and 30 are not counted or added since they have no close date.
When doing it by Cat
Cls Date Month | Cat | Count of Distinct Key 1 where Total Amount <> 0 | Sum of Amount | Note |
Jan, 2020 | A | 2 | 10+20+40=70 | Counted A1, A6 since it is 40 for Cat A Not counted A2 since 5-5=0, A4, A5 since 50-50= 0 |
Jan, 2020 | B | 1 | -40 | Counted A6 since it is -40 for Cat B Not counted A3 since 0=0 |
Average Cat A 70/2=35
Average Cat B -40/1=-40
Since the user can pick Cat via a filter visual on the screen.
How would I do this?
Fernando
Solved! Go to Solution.
@FPP Try a meausre like
divide( sum(Table[Amount])
, Countx(filter(summarize(Table, Table[Key 1] , "_1", sum(Table[Amount])), [_1] > 0 ), [Key 1])
)
@FPP Try a meausre like
divide( sum(Table[Amount])
, Countx(filter(summarize(Table, Table[Key 1] , "_1", sum(Table[Amount])), [_1] > 0 ), [Key 1])
)
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 |
---|---|
106 | |
97 | |
80 | |
67 | |
63 |
User | Count |
---|---|
147 | |
110 | |
108 | |
85 | |
64 |