Calculated Measure, then Regroup

[Anonymous]

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])))

Then, I created a parameter in the report:

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.

[v-xicai]

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.

[Anonymous]

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?

[v-xicai]

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.

[Iamnvt]

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:

https://1drv.ms/u/s!Aps8poidQa5zk6pUZAHa3CKJ1Rbm0Q