Power Query for Multiple category

[dilipan_mp]

Dear All,

I am having below table in Power BI. I am trying to find the query to do category wise summation and percentage.  Can you help us how to do in Power BI for the required output?

In the required output, the following field required

1) Total Production = Produced units KPI Category production wise

2) % of Production = Produced units KPI production-wise/Total Produced units KPI Production-wise

3) % of Power = Total Power consumed where KPI category =Power wise * % of Production

Regards

Dilipan

Month	City	KPI category	KPI	KPI value	KPI unit			Required output
June,2020	City A	Power	PC1	500	KWH/Month				Total Production	% of Total Production	Total Production/Total Power
June,2020	City A	Power	PC2	0	KWH/Month		City A		500	100%	500
June,2020	City A	Power	PC3	0	KWH/Month			Product1	500	100%	1
June,2020	City B	Power	PC1	600	KWH/Month			Product2	0	0%	0
June,2020	City B	Power	PC2	10	KWH/Month			Product3	0	0%	0
June,2020	City B	Power	PC3	20	KWH/Month		City B		300	100%	630
June,2020	City C	Power	PC1	200	KWH/Month			Product1	0	0%	0
June,2020	City C	Power	PC2	0	KWH/Month			Product2	100	33%	208
June,2020	City C	Power	PC3	80	KWH/Month			Product3	200	67%	422
June,2020	City A	Production	Product1	500	Units		City C		110		280
June,2020	City A	Production	Product2	0	Units			Product1	60	55%	153
June,2020	City A	Production	Product3	0	Units			Product2	0	0%	0
June,2020	City B	Production	Product1	0	Units			Product3	50	45%	127
June,2020	City B	Production	Product2	100	Units
June,2020	City B	Production	Product3	200	Units
June,2020	City C	Production	Product1	60	Units
June,2020	City C	Production	Product2	0	Units
June,2020	City C	Production	Product3	50	Units

[v-alq-msft · Accepted solution]

Hi, @dilipan_mp 

Based on your description, I'd like to suggest you try creating measures and displaying the result in a matrix visual. I created data to reproduce your scenario.

Table:

You may create three measures as below.

Total Production = 
var txt = LEFT(SELECTEDVALUE('Table'[KPI]),2)
return
IF(
    ISINSCOPE('Table'[KPI]),
    IF(
        txt<>"PC",
        SUM('Table'[KPI Value])
    ),
    IF(
        ISINSCOPE('Table'[City]),
        CALCULATE(
            SUM('Table'[KPI Value]),
            FILTER(
                'Table',
                'Table'[KPI Category]="Production"
            )
        ),
        CALCULATE(
            SUM('Table'[KPI Value]),
            FILTER(
                'Table',
                'Table'[KPI Category]="Production"
            )
        )
    )
)

% of Total Production = 
var txt = LEFT(SELECTEDVALUE('Table'[KPI]),2)
return
IF(
    ISINSCOPE('Table'[KPI]),
    IF(
        txt<>"PC",
        DIVIDE(
           SUM('Table'[KPI Value]),
           CALCULATE(
               SUM('Table'[KPI Value]),
               FILTER(
                   ALLEXCEPT('Table','Table'[City]),
                   'Table'[KPI Category]="Production"
               )
           )
        )
    ),
    IF(
        ISINSCOPE('Table'[City]),
        1,
        DISTINCTCOUNT('Table'[City])
    )
)

Total Production/Total Power = 
var txt = LEFT(SELECTEDVALUE('Table'[KPI]),2)
return
IF(
    ISINSCOPE('Table'[KPI]),
    IF(
        txt<>"PC",
        [% of Total Production]*
        CALCULATE(
            SUM('Table'[KPI Value]),
            FILTER(
                ALLEXCEPT('Table','Table'[City]),
                'Table'[KPI Category]="Power"
            )
        )
    ),
    IF(
        ISINSCOPE('Table'[City]),
        CALCULATE(
            SUM('Table'[KPI Value]),
            FILTER(
                ALLEXCEPT('Table','Table'[City]),
                'Table'[KPI Category]="Power"
            )
        ),
        CALCULATE(
            SUM('Table'[KPI Value]),
            FILTER(
                'Table',
                'Table'[KPI Category]="Power"
            )
        )
    )
)

Result:

Best Regards

Allan

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

[dilipan_mp]

Thank You so much for taking time and helping to create the query and Power BI file also.

God Bless you.

[dilipan_mp]

Hi,

Thanks for your reply.

Column city is common for Production and Power

[Fowmy]

@dilipan_mp 

What is the common key or a column that defines the link between Production and Power, for example, Product 1 and PC1, you cannot hard code, you need to have some relationship then you can merge and group and do the calculation easy to get the results.

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn