topic Re: Conditionnal sum, with conditions between row in DAX Commands and Tips

Conditionnal sum, with conditions between row

jct999 — Fri, 19 Nov 2021 18:27:27 GMT

Hi All,

I have a table with 5 columns :

DIMENSION_1 (string)
DIMENSION_2 (string)
DIMENSION_3 (string)
KPI_TYPE (string)

VOLUME (integer)

I would like to create a measure that sum VOLUME with the following conditions :

For a each underlying tuple (DIMENSION_1, DIMENSION_2, DIMENSION_3) :

If VOLUME exists for KPI_TYPE = "TYPE1"
Then add volume for KPI_TYPE = "TYPE_1" to the sum result
Else add volume for KPI_TYPE = "TYPE_2" to the sum result

I would say it is like an iterator SUM with conditions between rows ...

Could you help me to create such a measure

Thanks

Re: Conditionnal sum, with conditions between row

VahidDM — Sat, 20 Nov 2021 00:10:14 GMT

Hi jct999

Can you post sample data as text and expected output?
Not enough information to go on;

please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
4. Relation between your tables

Appreciate your Kudos!!
LinkedIn:www.linkedin.com/in/vahid-dm/

Re: Conditionnal sum, with conditions between row

jct999 — Sat, 20 Nov 2021 09:17:54 GMT

Hi VahidDM

Thanks for your message
Here's a sample of the table that contains the data :

DIMENSION1	DIMENSION2	DIMENSION3	KPI_TYPE	VOLUME
A1	B2	...	KT_1	97
A1	B2	...	KT_2	104
A1	B1	...	KT_2	108
A1	B2	...	KT_1	100
A2	B2	...	KT_2	99
A1	B3	...	KT_1	96
A1	B3	...	KT_2	93
A2	B1	...	KT_1	107
A2	B1	...	KT_2	90
A2	B2	...	KT_2	103
A2	B1	...	KT_1	102
A2	B1	...	KT_2	92
A2	B3	...	KT_1	92
A2	B3	...	KT_2	88
A1	B2	...	KT_3	50
...	...	...	...	...

For a better understanding, I pivot the KPI_TYPE column and color some numbers :

			KPI_TYPE
DIMENSION1	DIMENSION2	DIMENSION3	KT_1	KT_2	KT_3
A1	B2	...	97	104	50
A1	B1	...	(empty)	108	...
A1	B2	...	100	99	...
A1	B3	...	96	93	...
A2	B1	...	107	90	...
A2	B2	...	(empty)	103	...
A2	B1	...	102	92	...
A2	B3	...	92	88	...
...	...	...	...	...	...

Considering this reshaped view :
I want my measure to sum all the green values with the following rules : For each rows, sum the value in column KT_1 if it exists, otherwise sum the value in the column KT_2.

Obviously, the data could be filtered by the context/user of the dashboard, and all the dimensions could not be displayed in the widgets....

Let me know if it is not clear.

Best Regards

Re: Conditionnal sum, with conditions between row

VahidDM — Sat, 20 Nov 2021 12:23:35 GMT

Hi jct999

Understand your data table is like the Pivot table, so Please try this measure:

Measure= Var _K1 = SUM(table[KT_1] Var _K2 = SUM(table[KT_2] Var _A = addcolumn(Table,"K12",if(isblank(_K1),_K2,_K1)) return SUMX(_A,[K12])

Also, you can add a new column with Dax code to merge KT1 and KT2 and then use that new column:

Code to add a new Column:

Column = Var _K1 = SUM(table[KT_1] Var _K2 = SUM(table[KT_2] return if(isblank(_K1),_K2,_K1))

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

Re: Conditionnal sum, with conditions between row

jct999 — Sat, 20 Nov 2021 13:06:21 GMT

Hi VahidDM,

Unfortunatly, my table is not a pivot table.
It is a flat table with 5 columns (DIMENSION1 to 3, KPY_TYPE and VOLUME).
(see first table)

I showed you pivoted data to give you more clear understanding of my need and rules calculation.

Any idea ?

Thanks for your help

Re: Conditionnal sum, with conditions between row

Anonymous — Tue, 23 Nov 2021 02:31:04 GMT

Hi jct999 ,

You can create a matrix visual just as shown in below screenshot, please find the attachment for the details.

If the above one is not what you want, please provide more details of your expected result with calculation logic and special examples. Thank you.

Best Regards

Re: Conditionnal sum, with conditions between row

Anonymous — Mon, 06 Dec 2021 09:59:04 GMT

Hi jct999 ，

Is there anything else you need help with regarding this post? If not, could you please mark the helpful post as Answered? It will help the others in the community who have similar problems to yours to find a solution as soon as possible. Thank you.

Best Regards