cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
YuliyaMusiyuk
Regular Visitor

% Of revenue per outlet

Hello,

i am trying to calculate % of revenue per outlet; see below - i need each Cat-Category to be devided by Total rev in that Cat-DepartmentDesc

YuliyaMusiyuk_0-1628529033550.png

1 ACCEPTED SOLUTION
Eyelyn9
Community Support
Community Support

Hi @YuliyaMusiyuk ,

 

Please try the following formula :

div by rev =
VAR _rev =
    CALCULATE ( SUM ( 'Table'[REV] ), ALLEXCEPT ( 'Table', 'Table'[Cat-Depart] ) )
VAR _mtd =
    CALCULATE (
        SUM ( 'Table'[CY MTD] ),
        FILTER (
            'Table',
            'Table'[Cat-Depart] = MAX ( 'Table'[Cat-Depart] )
                && 'Table'[Cat-Category] = MAX ( 'Table'[Cat-Category] )
        )
    )
RETURN
    DIVIDE ( _mtd, _rev )

Then format the measure as 2 decimal places. The final output is shown below:

Two decimal places.PNGdiv by rev.PNG

Best Regards,
Eyelyn Qin
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

2 REPLIES 2
Eyelyn9
Community Support
Community Support

Hi @YuliyaMusiyuk ,

 

Please try the following formula :

div by rev =
VAR _rev =
    CALCULATE ( SUM ( 'Table'[REV] ), ALLEXCEPT ( 'Table', 'Table'[Cat-Depart] ) )
VAR _mtd =
    CALCULATE (
        SUM ( 'Table'[CY MTD] ),
        FILTER (
            'Table',
            'Table'[Cat-Depart] = MAX ( 'Table'[Cat-Depart] )
                && 'Table'[Cat-Category] = MAX ( 'Table'[Cat-Category] )
        )
    )
RETURN
    DIVIDE ( _mtd, _rev )

Then format the measure as 2 decimal places. The final output is shown below:

Two decimal places.PNGdiv by rev.PNG

Best Regards,
Eyelyn Qin
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

ryan_mayu
Super User
Super User

@YuliyaMusiyuk 

could you pls provide some sample data?  I am confused about your description and screenshot.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors