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.
