cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

Dynamic calculation based on multiple slicer

Hi All,

I could use some help here..

I want to make a calculation based on multiple slicer selection and I want to show it in a table

Slicer 1: Date >>> To show Output of the selected date based on selected variable in slicer 2
Slicer 2: NPO , CT , IP , PG , FA , AH , AP ,AT ,UD >>> variables

Here is my formula:
CNPO = (NPO +(CT*1000))* IP * PG * FA * AH * AP * AT * UD

Calculation needed:

If only NPO is selected in slicer 2 & 3-Mar-14 is selected in slicer 1
measure should be CNPO = 760543

If NPO and some other valuein slicer 2 (ex. CT) are selected in slicer 2 & 3-Mar-14 in slicer 1is selected
measure should be CNPO = (760543 + (1.02049*1000)) = 761,563.49

If NPO and some other value (ex. CT,IP and PG) are selected in slicer 2 & 27-Nov-14 in slicer 1 is selected
measure should be CNPO = (786863.6115 + (0.154227*1000)) * 1.000667 * 0.999548 = 787,186.81

If NPO and all other value are selected & 27-Nov-14 in slicer 1 is selected
measure should be CNPO = (786863.6115 + (0.154227*1000)) * 0.961114 * 1.004037 * 0.999307 * 0.999554 * 0.999548 * 1.000667 * 1.013536 = 769036.2286

This is my data

I made the table like this because I want to use it easily with my measure, but the output is not what I expected

My measure:

(CNPO =
( CALCULATE (SUM('WN-PO'[PO:NPO]),VALUES ( 'WN-PO'[CF] ), VALUES ( 'WN-PO'[Date]))
+( (CALCULATE (SUM('WN-PO'[PO:CT]),VALUES ( 'WN-PO'[CF] ),VALUES ( 'WN-PO'[Date]))) * 1000) )
* ( CALCULATE (SUM('WN-PO'[PO:AT]),VALUES ( 'WN-PO'[CF] ),VALUES ( 'WN-PO'[Date])))
* ( CALCULATE (SUM('WN-PO'[PO:AP]),VALUES ( 'WN-PO'[CF] ),VALUES ( 'WN-PO'[Date])))
* ( CALCULATE (SUM('WN-PO'[PO:AH]),VALUES ( 'WN-PO'[CF] ),VALUES ( 'WN-PO'[Date])))
* ( CALCULATE (SUM('WN-PO'[PO:FA]),VALUES ( 'WN-PO'[CF] ),VALUES ( 'WN-PO'[Date])))
* ( CALCULATE (SUM('WN-PO'[PO:PG]),VALUES ( 'WN-PO'[CF] ),VALUES ( 'WN-PO'[Date])))
* ( CALCULATE (SUM('WN-PO'[PO:IP]),VALUES ( 'WN-PO'[CF] ),VALUES ( 'WN-PO'[Date])))
* ( CALCULATE (SUM('WN-PO'[PO:UD]),VALUES ( 'WN-PO'[CF] ),VALUES ( 'WN-PO'[Date])))
Problem: I have to select all of the variables in slicer 2 to show the expected output CNPO that calculate from all selected variables
and I can't select just 2, 3 or maybe one to make a corrected calculation (The output CNPO is disappear from the table )

This is my expected table (the values is just an example):

I'm not sure whether this is possible or not to do this in power bi.

But, Thanks a lot for your support and any help is appreciated!

1 ACCEPTED SOLUTION
Community Support

I suggest you to combine all XX:PO columns to a same column. In Power BI, we always calculate values from same columns. Then add an Index column in your data model. You can add it directly in your data source or you can add this column by Power Query.

For reference: How to create group index with Power Query or R

You table will look like as below.

Then build a measure.

``````CNPO =
VAR _NPO =
SUMX ( FILTER ( 'Table', 'Table'[CF] = "NPO" ), 'Table'[PO] )
VAR _MaxIndex =
MAX ( 'Table'[Index] )
VAR _MaxIndexValue =
CALCULATE (
SUM ( 'Table'[PO] ),
FILTER (
ALL ( 'Table' ),
'Table'[Index] <> 1
&& 'Table'[Index] = _MaxIndex
&& 'Table'[Date] = MAX ( 'Table'[Date] )
)
)
VAR _List =
CALCULATETABLE (
VALUES ( 'Table'[PO] ),
FILTER ( 'Table', 'Table'[Index] <> _MaxIndex && 'Table'[Index] <> 1 )
)
VAR _Product =
IF (
MAX ( 'Table'[Index] ) = 1
|| DISTINCTCOUNT ( 'Table'[Index] ) = 2,
1,
PRODUCTX ( _List, [PO] )
)
RETURN
IF (
ISFILTERED ( 'Table'[CF] ),
( _NPO + _MaxIndexValue * 1000 ) * _Product,
BLANK ()
)``````

Result is as below. By default it will show blank.

If only NPO is selected in slicer 2 & 3-Mar-14 is selected in slicer 1
measure should be CNPO = 760543

If NPO and some other valuein slicer 2 (ex. CT) are selected in slicer 2 & 3-Mar-14 in slicer 1is selected
measure should be CNPO = (760543 + (1.02049*1000)) = 761,563.49

If NPO and all other value are selected & 27-Nov-14 in slicer 1 is selected
measure should be CNPO = (786863.6115 + (0.154227*1000)) * 0.961114 * 1.004037 * 0.999307 * 0.999554 * 0.999548 * 1.000667 * 1.013536 = 769036.2286

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 REPLIES 4
Frequent Visitor

Hi @v-rzhou-msft Thank you so much for your help. Your suggestion is very helpful for others and especially for me! Creating the data model like this is great and it is easier for working than my previous one. I think I have to study more about DAX functions that I do not understand from the measure you created. And, Thanks again, Rico!

Frequent Visitor

Community Support

I suggest you to combine all XX:PO columns to a same column. In Power BI, we always calculate values from same columns. Then add an Index column in your data model. You can add it directly in your data source or you can add this column by Power Query.

For reference: How to create group index with Power Query or R

You table will look like as below.

Then build a measure.

``````CNPO =
VAR _NPO =
SUMX ( FILTER ( 'Table', 'Table'[CF] = "NPO" ), 'Table'[PO] )
VAR _MaxIndex =
MAX ( 'Table'[Index] )
VAR _MaxIndexValue =
CALCULATE (
SUM ( 'Table'[PO] ),
FILTER (
ALL ( 'Table' ),
'Table'[Index] <> 1
&& 'Table'[Index] = _MaxIndex
&& 'Table'[Date] = MAX ( 'Table'[Date] )
)
)
VAR _List =
CALCULATETABLE (
VALUES ( 'Table'[PO] ),
FILTER ( 'Table', 'Table'[Index] <> _MaxIndex && 'Table'[Index] <> 1 )
)
VAR _Product =
IF (
MAX ( 'Table'[Index] ) = 1
|| DISTINCTCOUNT ( 'Table'[Index] ) = 2,
1,
PRODUCTX ( _List, [PO] )
)
RETURN
IF (
ISFILTERED ( 'Table'[CF] ),
( _NPO + _MaxIndexValue * 1000 ) * _Product,
BLANK ()
)``````

Result is as below. By default it will show blank.

If only NPO is selected in slicer 2 & 3-Mar-14 is selected in slicer 1
measure should be CNPO = 760543

If NPO and some other valuein slicer 2 (ex. CT) are selected in slicer 2 & 3-Mar-14 in slicer 1is selected
measure should be CNPO = (760543 + (1.02049*1000)) = 761,563.49

If NPO and all other value are selected & 27-Nov-14 in slicer 1 is selected
measure should be CNPO = (786863.6115 + (0.154227*1000)) * 0.961114 * 1.004037 * 0.999307 * 0.999554 * 0.999548 * 1.000667 * 1.013536 = 769036.2286

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Frequent Visitor

Hi Mr. RicoZhou

I have some problem with this model.

If NPO and some other value in slicer 2 (ex. only AT) are selected in slicer 2 & 27-Nov-14 in slicer 1 is selected
measure should be CNPO = (786863.6115+(0*1000)) * (0.961114)

BUT the measure seems to be CNPO = (786863.6115 + (0.961114*1000)) * 1

which is a wrong calculation because  AT (0.961114) becomes _MaxValueIndex (instead of CT(0.154227) as CT is unselected) (If CT is unselected, the value has to be 0) (Unselected values in slicer 2 are equal 1 except CT)

Thanks a lot for your support and any help is appreciated!

Announcements

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors