Forum Discussion
Calculated Measure, then Regroup
Dears,
I'm having a terrible problem while trying to make a "dynamic" but simple report.
Imagine that I have the following data:
| PRODUCT | CONSUMER | ID | VALUE | ISSUER | DATE | DOCUMENTATION |
| A | 456 | 78278 | 5,249.68 | Issuer 3 | 30/05/2019 | NULL |
| B | 9753 | 4564532 | 81,150.88 | Issuer 3 | 30/05/2019 | NULL |
| C | 9753 | 456452 | 142,926.87 | Issuer 2 | 30/05/2019 | NULL |
| A | 97513 | 7897897 | 230,296.42 | Issuer 2 | 30/05/2019 | OK |
| A | 815 | 45645 | 228,506.81 | Issuer 2 | 30/05/2019 | OK |
| A | 9999 | 456 | 208,067.60 | Issuer 1 | 30/05/2019 | OK |
| D | 97513 | 45678 | 301,265.52 | Issuer 1 | 30/05/2019 | OK |
| B | 9999 | 456787 | 202,363.33 | Issuer 1 | 30/05/2019 | OK |
| A | 815 | 0575637 | 187,206.10 | Issuer 1 | 30/05/2019 | OK |
| A | 97513 | 708678 | 116,712.27 | Issuer 4 | 30/05/2019 | OK |
So the first thing I need to do is sum up the Value grouping it by Issuer and Consumer (the product doesn't matter).
I created a concatenated field in my query with this two fields, and did the calculation this way:
Grouped_Value = SUMX(VALUES(Data[MyNewField]); CALCULATE(SUM(Data[Value])))
Limit = GENERATESERIES(10000; 300000; 10000)
So, the goal is, depending on the value the user selects, the Grouped_Value has to be recalculated:
If the Grouped_Value is bigger than the Limit, I subtract the Limit from the Grouped_Value:
OverLimit = IF([Grouped_Value]>Limit[Limit_Value]; [Grouped_Value]-Limit[Limit_Value];0)
But then I need to show this data "re-grouped" by ISSUER (not the concatenated field I created).
Which means, that the calculation (Grouped_Value-Limit) is evaluate by the ISSUER.
Clearer: When I display the data with the concatenated field, the calculation is right and the values is ok. But I display the data with the Issuer (without the concatenated field), the values from all consumers is sumed up and then the limit is applied - what is wrong.
I wish I was clear and hopefully expect that you can hep me.
4 Replies
Hi Anonymous ,
>>So the first thing I need to do is sum up the Value grouping it by Issuer and Consumer (the product doesn't matter).
For this demand, you can create measure like DAX below.
Measure1 = CALCULATE(SUM(Data[VALUE]),FILTER(ALLSELECTED(Data), Data[ISSUER] =MAX(Data[ISSUER])&&Data[CONSUMER] =MAX(Data[CONSUMER])))
I am not sure what desired result would you want, could you please share your sample data and desired output screenshots for further analysis? You can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.
Best Regards,
Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
HI v-xicai .
Thanks for your help. Power BI is something new to me.
Actually, your ideia dind't work as I expected.
But I didnt make myself clear enough.
Let's imagine, I working with that table:
PRODUCT CONSUMER ID VALUE ISSUER DATE DOCUMENTATION Limit (End User Defined) A 456 78278 5,249.68 Issuer 3 30/05/2019 NULL 150,000 B 9753 4564532 81,150.88 Issuer 3 30/05/2019 NULL 150,000 C 9753 456452 142,926.87 Issuer 2 30/05/2019 NULL 150,000 A 97513 7897897 230,296.42 Issuer 2 30/05/2019 OK 150,000 A 815 45645 228,506.81 Issuer 2 30/05/2019 OK 150,000 A 9999 456 208067.60 Issuer 1 30/05/2019 OK 150,000 D 97513 45678 301,265.52 Issuer 1 30/05/2019 OK 150,000 B 9999 456787 202,363.33 Issuer 1 30/05/2019 OK 150,000 A 815 575637 187206.10 Issuer 1 30/05/2019 OK 150,000 A 97513 708678 116,712.27 Issuer 4 30/05/2019 OK 150,000 The user will set the limit value (I made a parameter available for that).
If, for instance, the sum of all values of Client A, referring to the Issuer 1 (208,067.6+187,206.1=395,273.7) is greater than that limit (150,000) and the Client doesn't have the documentation, I'll need to know the difference (395,273.7-150,000=245.273,7). If the cliente have the doc, there is no risk, so the value is zero.
That is kind of easy to do (in a spresheet), but I intend to make a report grouping, at the end, this difference by Issuer.
Is it clear?
hi,
you can use this measure to calculate the group:
Grouped_value = CALCULATE(SUM(Table1[VALUE]), ALL(Table1),VALUES(Table1[PRODUCT]), VALUES(Table1[ISSUER]))
here is the PBI file:
