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.
Let's start with the data for demontration:
id | datasize | action | Custom column mentioned below | category |
1 | 9 | create | 3 | category1 |
1 | 9 | read | 3 | category1 |
2 | 8 | create | 4 | category2 |
1 | 9 | read | 3 | category1 |
2 | 8 | read | 4 | category2 |
3 | 10 | create | 10 | category1 |
The goal is to sum the datasize for all category, but if I simply sum the datasize column, then the same document's size is being summed multiple times, which is obviously not good. Therefore, I came up with the following custom column that divides the datasize by number of rows with the same id:
It shows the size correctly, until I use filters on the action column. For example, if only "read" is selected, then the document (with id=1) sum gets reduced to 6 instead of 9, because the line with "create" action is no longer being summed.
How can I show the datasize correctly every time, when other filters are being applied as well?
Solved! Go to Solution.
Hi,
You do not need a calculated column formula. Try this measure
Min datasize = MIN(Data[Datasize])
Measure = SUMX(SUMMARIZE(VALUES(Data[Id]),Data[Id],"ABCD",[Min datasize]),[ABCD])
Hope this helps.
Hi,
What result are you expecting when Read is selected? There are 2 ID's for Read - 1 and 2. Should the answer be 9+8 = 17?
Please clarify.
Hi @Ashish_Mathur ,
Yes, that is correct.
Maybe the I downgraded the probelem way too much. I have an additional category column which I am curious about.
id | datasize | action | Custom column mentioned below | category |
1 | 9 | create | 3 | category1 |
1 | 9 | read | 3 | category2 |
2 | 8 | create | 4 | category2 |
1 | 9 | read | 3 | category1 |
2 | 8 | read | 4 | category2 |
so the question I am trying to answear is the following: what is the datasize sum for each category, but obviously each document should be counted only once.
Hi,
You do not need a calculated column formula. Try this measure
Min datasize = MIN(Data[Datasize])
Measure = SUMX(SUMMARIZE(VALUES(Data[Id]),Data[Id],"ABCD",[Min datasize]),[ABCD])
Hope this helps.
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 |
---|---|
112 | |
100 | |
77 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |