Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
gjgj111111
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

gjgj111111_2-1629174220907.png

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): 

 

gjgj111111_1-1629173366962.png

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

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.

1.png

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.

1.png

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

1.png

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

2.png

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

3.png

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.

View solution in original post

4 REPLIES 4
gjgj111111
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!

gjgj111111
Frequent Visitor

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

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.

1.png

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.

1.png

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

1.png

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

2.png

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

3.png

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.

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!

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.