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 required1) Total Production = Produced units KPI Category production wise2) % of Production = Produced units KPI production-wise/Total Produced units KPI Production-wise3) % of Power = Total Power consumed where KPI category =Power wise * % of Production RegardsDilipanMonthCityKPI categoryKPIKPI valueKPI unit Required output June,2020City APowerPC1500KWH/Month Total Production% of Total ProductionTotal Production/Total PowerJune,2020City APowerPC20KWH/Month City A 500100%500June,2020City APowerPC30KWH/Month Product1500100%1June,2020City BPowerPC1600KWH/Month Product200%0June,2020City BPowerPC210KWH/Month Product300%0June,2020City BPowerPC320KWH/Month City B 300100%630June,2020City CPowerPC1200KWH/Month Product100%0June,2020City CPowerPC20KWH/Month Product210033%208June,2020City CPowerPC380KWH/Month Product320067%422June,2020City AProductionProduct1500Units City C 110 280June,2020City AProductionProduct20Units Product16055%153June,2020City AProductionProduct30Units Product200%0June,2020City BProductionProduct10Units Product35045%127June,2020City BProductionProduct2100Units June,2020City BProductionProduct3200Units June,2020City CProductionProduct160Units June,2020City CProductionProduct20Units June,2020City CProductionProduct350Units

New Member

5 years ago

Solved

Power Query for Multiple category

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

Need Help

v-alq-msft

5 years ago

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.

Power Query for Multiple category.pbix41 KB

4 Replies

Fowmy
Super User
5 years ago
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

v-alq-msft

Community Support

5 years ago

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.

Power Query for Multiple category.pbix41 KB

dilipan_mp
New Member
5 years ago
Thank You so much for taking time and helping to create the query and Power BI file also.

God Bless you.

dilipan_mp
New Member
5 years ago
Hi,

Thanks for your reply.

Column city is common for Production and Power

Forum Discussion

Power Query for Multiple category

4 Replies

Related content

Ranking Sales By Multiple Categories

SUMX Group By Multiple Categories

power bi report based on multiple semantic models

Weighted Average for Multiple Categories

Average of categories over vendor