cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Max_Z
Microsoft
Microsoft

Who was most contributing customer (and how much increased)

Hi PowerBI gurus !

 

I have build report that display protection level of the customers by amount of machines...
I am trying to build graph\chart\table that will display who was most contributing in the timeframe, who increased the most or alternate some table that shows up and down arrow with numbers on how much value increased...

example of table:
Screenshot_1.jpg

1 ACCEPTED SOLUTION
v-lid-msft
Community Support
Community Support

Hi @Max_Z ,

 

We can create a measure to show the increasement for each org

 

Increasement =
VAR maxDay =
    MAX ( 'Table'[Day] )
VAR minDay =
    MIN ( 'Table'[Day] )
RETURN
    CALCULATE ( SUM ( 'Table'[MachinesInGroup] ), 'Table'[Day] = maxDay )
        - CALCULATE ( SUM ( 'Table'[MachinesInGroup] ), 'Table'[Day] = minDay )

 

Then create another measure to get the most contributor

 

MostContributor = 
VAR t =
    SUMMARIZECOLUMNS ( 'Table'[OrgId], "Total", [Increasement] )
RETURN
    CALCULATE (
        MAXX ( FILTER ( t, [Increasement] = MAXX ( t, [Increasement] ) ), [OrgId] )
    )

24.PNG25.PNG

 

If it doesn't meet your requirement, Please show the exact expected result based on the Tables that you have shared.

 


BTW, pbix as attached.

 

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Dong Li
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

5 REPLIES 5
v-lid-msft
Community Support
Community Support

Hi @Max_Z ,

 

We can createa measure to display who was most contributing in the timeframe:

 

TOPFGroup = MAXX(TOPN(1,'Table',[MachinesInGroup],DESC),[FGroupID])

5.PNG

Could you please provide more details about increased the most? What time range you want to compare with?

 

If it doesn't meet your requirement, Please show the exact expected result based on the Tables that you have shared.

 


BTW, pbix as attached.

 

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Dong Li,

 

It is not what I am looking for...

Provided measure shows wich group have most machines...

 

I need to calculate what is the increasing rate of machines in group by customer.

example table:

DayOrgIdRbacGroupIdProtectionLevelIdProtectionLevel_nameMachinesInGroup
17-09-19 00:00x1233FullyAutomated10000
18-09-19 00:00x1233FullyAutomated11000
19-09-19 00:00x1233FullyAutomated10500
17-09-19 00:00y2343FullyAutomated20000
18-09-19 00:00y2343FullyAutomated21000
19-09-19 00:00y2343FullyAutomated22000

 

So following that table, I need to calculate how much machines increased for each customer for entire dates (in that example 17-19) and report should be able to adapt to modification of the dates (so if I select specific dates in the report it will display data for these dates only).

v-lid-msft
Community Support
Community Support

Hi @Max_Z ,

 

We can create a measure to show the increasement for each org

 

Increasement =
VAR maxDay =
    MAX ( 'Table'[Day] )
VAR minDay =
    MIN ( 'Table'[Day] )
RETURN
    CALCULATE ( SUM ( 'Table'[MachinesInGroup] ), 'Table'[Day] = maxDay )
        - CALCULATE ( SUM ( 'Table'[MachinesInGroup] ), 'Table'[Day] = minDay )

 

Then create another measure to get the most contributor

 

MostContributor = 
VAR t =
    SUMMARIZECOLUMNS ( 'Table'[OrgId], "Total", [Increasement] )
RETURN
    CALCULATE (
        MAXX ( FILTER ( t, [Increasement] = MAXX ( t, [Increasement] ) ), [OrgId] )
    )

24.PNG25.PNG

 

If it doesn't meet your requirement, Please show the exact expected result based on the Tables that you have shared.

 


BTW, pbix as attached.

 

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Dong Li
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

Great, thank you @v-lid-msft .

 

Just one question, is it possible to show several (top3/5) contributors instead of one ?

v-lid-msft
Community Support
Community Support

Hi @Max_Z ,

 

We can create a measure using following formula then put it into the Visual Filter, set the condion to meet your requirement

 

IncreseRank =
VAR t =
    ADDCOLUMNS (
        GROUPBY ( ALLSELECTED ( 'Table' ), 'Table'[OrgId] ),
        "Total", [Increasement]
    )
RETURN
    RANKX ( t, [Total], [Increasement] )

1.PNG

 


BTW, pbix as attached.

 

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors