Dynamic calculation based on multiple slicer

[gjgj111111]

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! 

[gjgj111111]

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!

[gjgj111111]

If you have any ideas or suggestions, please help me.

[v-rzhou-msft · Accepted solution]

Hi @gjgj111111 

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.

[gjgj111111]

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!