Forum Discussion

gjgj111111's avatar
gjgj111111
Frequent Visitor
5 years ago
Solved

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! 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

4 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        gjgj111111
        Frequent Visitor
        4 years ago

        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!

         

  • gjgj111111's avatar
    gjgj111111
    Frequent Visitor
    4 years ago

    Hi Anonymous 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!