Forum Discussion
Calculated Measure, then Regroup
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:
Hi Anonymous ,
You can create measure like DAX below.
OverLimit = Var d=CALCULATE(SUM(Data[VALUE]),FILTER(ALLSELECTED(Data), Data[ISSUER] =MAX(Data[ISSUER])&&Data[PRODUCT] =MAX(Data[PRODUCT]))) Return IF(d>Limit[Limit_Value]&&MAX(Data[DOCUMENTATION])="NULL", d-Limit[Limit_Value], 0)
Best Regards,
Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
