Measure question

[Anonymous]

Hi friends, 

I have the following question. 

I have a dataset with a column were two order types ('Mandaatopdr. met contract' and 'Mandaatopdr. zonder contract') are stated. 

Each order (row) has a mandate amount and after the job is done and we receive an invoice, the row will have an invoiced amount as well.

For each order type I need to provide the following information:

Give per order type the sum of the values in the column mandate amount, but only when invoiced amount cell is 'blank'. (in other words; what is per order type the total value of the mandate amount of those orders were we haven't received an invoiced amount). 

Hope you can help me. Below you find an example of the data.

Many thanks in advance!

Cheers, Sander

Mld Melding Key	Mld Opdr Bedrijfopdr Volgnr	Order type	Order date	District	Invoiced amount	Mandate amount
77451	1	Mandaatopdr. zonder contract	1-11-2019	Noord	281,89	200
77452	1	Mandaatopdr. zonder contract	1-11-2019	Midden NL		500
77453	2	Mandaatopdr. zonder contract	1-11-2019	Den Haag		500
77467	2	Mandaatopdr. zonder contract	1-11-2019	Den Haag	135	500
77471	1	Mandaatopdr. zonder contract	1-11-2019	Rotterdam		500
77512	1	Mandaatopdr. met contract	1-11-2019	Noord		50
77515	1	Mandaatopdr. zonder contract	1-11-2019	Noord	337,35	400
77556	2	Mandaatopdr. met contract	1-11-2019	Oost		541
77558	1	Mandaatopdr. zonder contract	1-11-2019	Den Haag		472,5
77578	1	Mandaatopdr. met contract	1-11-2019	Amsterdam		900
77586	1	Mandaatopdr. zonder contract	1-11-2019	Oost		500
77587	1	Mandaatopdr. zonder contract	1-11-2019	Noord	491,2	500
77591	1	Mandaatopdr. zonder contract	1-11-2019	Den Haag	206	1500
77600	1	Mandaatopdr. zonder contract	1-11-2019	Oost		756
77600	2	Mandaatopdr. zonder contract	1-11-2019	Oost		805
77600	3	Mandaatopdr. zonder contract	1-11-2019	Oost		805
77600	4	Mandaatopdr. zonder contract	1-11-2019	Oost		100
77615	2	Mandaatopdr. met contract	1-11-2019	Noord		700
77683	2	Mandaatopdr. zonder contract	4-11-2019	Den Haag	225	500
77691	1	Mandaatopdr. zonder contract	4-11-2019	Rotterdam	410,21	500
77698	1	Mandaatopdr. met contract	4-11-2019	Midden NL		500
77709	1	Mandaatopdr. met contract	4-11-2019	Rotterdam		1055,8
77716	1	Mandaatopdr. zonder contract	4-11-2019	Midden NL		153,42
77716	2	Mandaatopdr. zonder contract	4-11-2019	Midden NL		153,42
77731	2	Mandaatopdr. zonder contract	4-11-2019	Amsterdam		150
77739	2	Mandaatopdr. zonder contract	4-11-2019	Midden NL		500
77754	1	Mandaatopdr. met contract	4-11-2019	Rotterdam		250
77758	1	Mandaatopdr. zonder contract	4-11-2019	Oost	111,22	145
77758	1	Mandaatopdr. zonder contract	4-11-2019	Oost	33,44	145
77759	1	Mandaatopdr. met contract	4-11-2019	Amsterdam		200
77767	2	Mandaatopdr. zonder contract	4-11-2019	Amsterdam		200
77774	1	Mandaatopdr. zonder contract	4-11-2019	Den Haag	74,48	500
77774	1	Mandaatopdr. zonder contract	4-11-2019	Den Haag	74,48	500
77774	1	Mandaatopdr. zonder contract	4-11-2019	Den Haag	37,24	500
77781	1	Mandaatopdr. met contract	4-11-2019	Amsterdam	51,68	50
77850	1	Mandaatopdr. met contract	5-11-2019	Den Haag	923,58	1000

[v-juanli-msft]

Hi @Anonymous 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case?

If not, please let me know.

Best Regards

Maggie

[Anonymous]

Hi Maggy,

Sorry for the delay. This friday, I have time to check the proposed solution (and accept it if it works).

Cheers, Sander 

[JarroVGIT · Accepted solution]

Always fun to create a measure here and there 🙂 Had some trouble in the beginning because I didn't do the formatting correcly (decimal numbers) but that was because of the locale settings of my Excel and PBI 😉

Anyway, here is the Measure you can use for it:

Measure = 
IF(HASONEVALUE('Table'[Order type]), 
    VAR orderType = SELECTEDVALUE('Table'[Order type])
    RETURN
    CALCULATE(SUM('Table'[Mandate amount]), FILTER(ALL('Table'), 'Table'[Order type] = orderType && ISBLANK('Table'[Invoiced amount]) = TRUE)))

Creating a Table visual with the column 'Order type' and the measure results in this:

Note that this measure only returns a result in a context of a single Order Type, and hence the total line is empty (as that evaluates the measure for the whole data set, so with two order types present). If you don't want that, you can simplify the measure like to this:

Measure = CALCULATE(SUM('Table'[Mandate amount]), FILTER('Table', ISBLANK('Table'[Invoiced amount]) = TRUE))

Resulting in the following table visual:

Let me know if this works! 🙂

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂