Solved: Calculation of baseline and issuemix values in pow...

shripad · ‎05-26-2022

Hi All

I am new to Power BI and stuck in a problem. There is a sample data below.


response_year	response_week	response_month	response_channel	marketplace_type	order_journey_node	count_B	count_A
2022	21	5	Survey	Local_shop	Karnataka	46	8
2022	21	5	Survey	Local_shop	Karnataka	9	1
2022	21	5	Survey	Local_shop	Karnataka	1	0
2022	21	5	Survey	Local_shop	Karnataka	11	4
2022	21	5	Survey	Local_shop	Odisha	1	0
2022	21	5	Survey	Local_shop	Odisha	1	0
2022	21	5	Survey	Local_shop	Chattisgarh	1	0
2022	21	5	Survey	Local_shop	Chattisgarh	1	0
2022	21	5	Survey	Local_shop	Chattisgarh	1	0
2022	21	5	Survey	Local_shop	Punjab	12	2
2022	21	5	Survey	Local_shop	Punjab	1	0
2022	21	5	Survey	Local_shop	Maharashtra	2	0
2022	21	5	Survey	Local_shop	Maharashtra	12	3
2022	21	5	Survey	Local_shop	Maharashtra	1	1
2022	21	5	Survey	Local_shop	Maharashtra	1	0
2022	21	5	Survey	Local_shop	UP	21	8
2022	21	5	Survey	Local_shop	UP	4	0
2022	21	5	Survey	Local_shop	UP	3	1
2022	21	5	Survey	Local_shop	UP	1	0
2022	21	5	Survey	Local_shop	Assam	41	5

i need the output like this

	Sum of count_B	Sum of RR
Row Labels	20	21	20	21	delta	baseline	issuemix
Assam	5.09%	6.64%	85.00%	14.00%	-71.00%	-3.6132315521628500%	0.22%
Chattisgarh	0.51%	0.40%	100.00%	0.00%	-100.00%	-0.5089058524173030%	0.00%
Gujrat	63.10%	60.96%	83.47%	31.37%	-52.10%	-32.8743202115452000%	-0.67%
Karnataka	3.05%	21.78%	66.67%	54.27%	-12.40%	-0.3785763048470180%	10.16%
Kerela	2.04%	0.80%	75.00%	16.67%	-58.33%	-1.1874469889737100%	-0.21%
Maharashtra	4.58%	2.12%	77.78%	25.00%	-52.78%	-2.4173027989821900%	-0.61%
Odisha	5.34%	0.27%	76.19%	0.00%	-76.19%	-4.0712468193384200%	0.00%
Punjab	0.51%	1.73%	50.00%	15.38%	-34.62%	-0.1761597181444510%	0.19%
Rajasthan	3.31%	1.46%	46.15%	9.09%	-37.06%	-1.2260004626416800%	-0.17%
UP	12.47%	3.85%	61.22%	31.03%	-30.19%	-3.7641484601210800%	-2.67%
Grand Total	100.00%	100.00%	78.12%	34.13%	-43.99%	-50.22%	6.23%

Sum of count_B has % of column total

Sum of RR = count_b / count_A

i wanted to know how to calculate baseline and issuemix formula in power bi from the sample data .

the baseline formula is : (Sum of RR[21] - Sum of RR[20]) * Sum of count_B[20]

the issuemix formula is: : (Sum of count_B[21] - Sum of count_B[20]) * Sum of RR[21]

i got this output in excel but the values in my dax it's showing error. Unable to find the logic of these 2 formula. Please help

v-shex-msft · ‎05-30-2022

HI @shripad,

You can take a look at the following formulas if help:

sum of countB =
CALCULATE (
    DIVIDE (
        CALCULATE ( SUM ( Table[count_B] ), VALUES ( Table[order_journey_node] ) ),
        SUM ( Table[count_B] )
    ),
    ALLSELECTED ( Table ),
    VALUES ( Table[response_year] ),
    VALUES ( Table[response_week] )
)

sum of countPR =
CALCULATE (
    DIVIDE ( SUM ( Table[count_B] ), SUM ( Table[count_A] ) ),
    ALLSELECTED ( Table ),
    VALUES ( Table[response_year] ),
    VALUES ( Table[response_week] ),
    VALUES ( Table[order_journey_node] )
)

Baseline =
VAR currWeek =
    MAX ( Table[response_week] )
RETURN
    CALCULATE (
        CALCULATE (
            DIVIDE ( SUM ( Table[count_B] ), SUM ( Table[count_A] ) ),
            Table[response_week] = currWeek
        ),
        CALCULATE (
            DIVIDE ( SUM ( Table[count_B] ), SUM ( Table[count_A] ) ),
            Table[response_week] = currWeek - 1
        ),
        ALLSELECTED ( Table ),
        VALUES ( Table[response_year] ),
        VALUES ( Table[order_journey_node] )
    )
        * CALCULATE (
            DIVIDE (
                CALCULATE ( SUM ( Table[count_B] ), VALUES ( Table[order_journey_node] ) ),
                SUM ( Table[count_B] )
            ),
            FILTER ( ALLSELECTED ( Table ), Table[response_week] = currWeek - 1 ),
            VALUES ( Table[response_year] )
        )

issuemix =
VAR currWeek =
    MAX ( Table[response_week] )
RETURN
    CALCULATE (
        CALCULATE (
            DIVIDE (
                CALCULATE ( SUM ( Table[count_B] ), VALUES ( Table[order_journey_node] ) ),
                SUM ( Table[count_B] )
            ),
            Table[response_week] = currWeek
        )
            - CALCULATE (
                DIVIDE (
                    CALCULATE ( SUM ( Table[count_B] ), VALUES ( Table[order_journey_node] ) ),
                    SUM ( Table[count_B] )
                ),
                Table[response_week] = currWeek - 1
            ),
        ALLSELECTED ( Table ),
        VALUES ( Table[response_year] )
    )
        * CALCULATE (
            CALCULATE (
                DIVIDE ( SUM ( Table[count_B] ), SUM ( Table[count_A] ) ),
                Table[response_week] = currWeek
            ),
            ALLSELECTED ( Table ),
            VALUES ( Table[response_year] ),
            VALUES ( Table[order_journey_node] )
        )

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

v-shex-msft · ‎05-30-2022

HI @shripad,

You can take a look at the following formulas if help:

sum of countB =
CALCULATE (
    DIVIDE (
        CALCULATE ( SUM ( Table[count_B] ), VALUES ( Table[order_journey_node] ) ),
        SUM ( Table[count_B] )
    ),
    ALLSELECTED ( Table ),
    VALUES ( Table[response_year] ),
    VALUES ( Table[response_week] )
)

sum of countPR =
CALCULATE (
    DIVIDE ( SUM ( Table[count_B] ), SUM ( Table[count_A] ) ),
    ALLSELECTED ( Table ),
    VALUES ( Table[response_year] ),
    VALUES ( Table[response_week] ),
    VALUES ( Table[order_journey_node] )
)

Baseline =
VAR currWeek =
    MAX ( Table[response_week] )
RETURN
    CALCULATE (
        CALCULATE (
            DIVIDE ( SUM ( Table[count_B] ), SUM ( Table[count_A] ) ),
            Table[response_week] = currWeek
        ),
        CALCULATE (
            DIVIDE ( SUM ( Table[count_B] ), SUM ( Table[count_A] ) ),
            Table[response_week] = currWeek - 1
        ),
        ALLSELECTED ( Table ),
        VALUES ( Table[response_year] ),
        VALUES ( Table[order_journey_node] )
    )
        * CALCULATE (
            DIVIDE (
                CALCULATE ( SUM ( Table[count_B] ), VALUES ( Table[order_journey_node] ) ),
                SUM ( Table[count_B] )
            ),
            FILTER ( ALLSELECTED ( Table ), Table[response_week] = currWeek - 1 ),
            VALUES ( Table[response_year] )
        )

issuemix =
VAR currWeek =
    MAX ( Table[response_week] )
RETURN
    CALCULATE (
        CALCULATE (
            DIVIDE (
                CALCULATE ( SUM ( Table[count_B] ), VALUES ( Table[order_journey_node] ) ),
                SUM ( Table[count_B] )
            ),
            Table[response_week] = currWeek
        )
            - CALCULATE (
                DIVIDE (
                    CALCULATE ( SUM ( Table[count_B] ), VALUES ( Table[order_journey_node] ) ),
                    SUM ( Table[count_B] )
                ),
                Table[response_week] = currWeek - 1
            ),
        ALLSELECTED ( Table ),
        VALUES ( Table[response_year] )
    )
        * CALCULATE (
            CALCULATE (
                DIVIDE ( SUM ( Table[count_B] ), SUM ( Table[count_A] ) ),
                Table[response_week] = currWeek
            ),
            ALLSELECTED ( Table ),
            VALUES ( Table[response_year] ),
            VALUES ( Table[order_journey_node] )
        )

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.