Zaynah16 Tracker

Indentation

Zaynah16 — Thu, 11 Aug 2022 10:11:37 GMT

Hi guys,

Please can someone help me i am trying to do indentation on in a table.

My data is structured as follows:

Re: Measure for Percentage difference

Zaynah16 — Thu, 23 Jun 2022 18:22:33 GMT

Hi SpartaBI

Another quiestion please

I've got a graph with the above data however when i click on the No data or lagging on the grapgh it gives me a percentage. How do i exclude lagging and no data and only have a result when clicking on the on track or at risk. if possible could we have it return N/A when clicking on the no data and lagging

Re: Measure for Percentage difference

Zaynah16 — Wed, 22 Jun 2022 14:53:04 GMT

Thank you! This worked!

Re: Measure for Percentage difference

Zaynah16 — Wed, 22 Jun 2022 13:00:36 GMT

SpartaBI

Sample data - i need a dax calc/ measure to calculate the percentage score.

eg.

289 items

79 are on track

60 are at risk

I need a percentage total score of the on track minus at risk

PILLAR	Business Unit	Section	Sub Section	Item	Status	Score
					On track	1
					On track	1
					On track	1
					Lagging	2
					Lagging	2
					Lagging	2
					Lagging	2
					No data	0
					No data	0
					No data	0
					No data	0
					At risk	3
					At risk	3
					At risk	3
					At risk	3
					At risk	3
					At risk	3

Measure for Percentage difference

Zaynah16 — Wed, 22 Jun 2022 12:30:55 GMT

Hi everyone

Please could someone help me with a measure

I need to calculate the percentage difference between two status's ( on track = 1 and at risk = 2)

Calculation

Total number % number of green(1) = percentage of green

Total number % total number of red (2) = percentage of red

percentage of green minus percentage of red = total score

Data Stucture

Tatble name : RAG status

Column 1 : Item

Column 2 :Status

Column 3: Score

Thanks in advance

Colour coding for rows with specific text

Zaynah16 — Wed, 16 Mar 2022 10:26:23 GMT

Hi Guys,

Please could someone help me with. I am trying to colour code specific rows with text in a table view . As per the image below.

Eg .

Type

Project (green)

Objective (blue)

Goal (red)

each row item as a colour linked to the type.

Thank you in adavance

Zaynah

Re: Transposing data

Zaynah16 — Thu, 03 Feb 2022 07:09:12 GMT

Hi edhans,

Thank you for your help! The reason for chnaging the data is for a specic view, and the current format was not working. I tried all of the above and they all worked however i went with doing Pivot as it was the simplest.

Thanks

Zaynah

Transposing data

Zaynah16 — Tue, 01 Feb 2022 15:59:37 GMT

Hi guys,

Please could someone help

I've got a dataset as follows:

Pillar Bu Sub Bu overallstrategic goal type& year

xxx xxyhy hyhhh Buy X litres of water annulised target 2022

xxx hhhh hyhhh Buy X litres of paint annulised target 2023

xxx xxyhy hyhhh Buy X litres of water annulised target 2024

xxx hhhh hyhhh Buy X litres of paint annulised target 2025

xxx hhhh hyhhh Buy X litres of paint overall stratetic target 2025

I need the table to have the follow columns

Pillar Bu Sub Bu Annualised target 2022 Annualised target 2023 Annualised target 2025 Annualised target 2025 overall tratetic target 2025

I need the values from type and year column to be as each column value with the strategic goal as the value matching up to each.

My data current sits in acess and i need to have the visual as above.

Thank you.

Re: CAGR for Group

Zaynah16 — Wed, 19 Jan 2022 16:18:49 GMT

Do you have a solution on how to calculate the CAGRS in power bi ?

Re: CAGR for Group

Zaynah16 — Wed, 19 Jan 2022 15:53:52 GMT

This is the power bi visual. The CAGR values are calculated in Acess SQL because this current table is a query table table from several other tables .

This is the SQL in acess

SELECT t1.pillar, t1.[Business Unit], t1.[Income type], t1.[Statement type], t1.Year AS yr_1, t2.Year AS yr_2, t1.value AS value_1, t2.value AS value_2, ((t1.value/t2.value)^(1/((IIf(t1.Year='2025 with Inorganic','2025',t1.Year))-(IIf(t2.Year='2025 with Inorganic','2025',t2.Year))))-1) AS cagr, IIf([t2].[Year]="2025 with Inorganic","Inorganic and Organic",
IIf([t2].[Year]="Net Organic Growth","Net Organic Growth","Organic")) AS [Growth Type]
FROM 20172025revenues AS t1 INNER JOIN 20172025revenues AS t2 ON (t1.pillar = t2.pillar) AND (t1.[Business Unit] = t2.[Business Unit]) AND (t1.[Income type] = t2.[Income type])
WHERE (((t1.[Business Unit])<>'Other And Consolidation Entries'));

CAGR for Group

Zaynah16 — Wed, 19 Jan 2022 15:11:16 GMT

HI guys,

Please could I get help with the following:

I ned to calculate a CAGR value for a group, I have done so with the individual business units on a link and stacked bar chart but I need a grop total as well

Sample Table

Table 1

Pillar Business Unit Income Type Statement Type yr1 yr2 value 1 value 2 CAGR Growth type

xxx xx Revenue Actual 2017 2018 444 874 76% inorganic

yyy yy Revenue Budget 2018 2017 7777 678 23% organic

zzz zz Revenue Actual 2019 2021 999 8653 87% inorganic

aaa aa Revenue Board Pack 2020 2017 8776 367 93% inorganic

bbb bb Revenue Actual 2021 2023 867 355 23% inorganic

ccc cc Revenue Actual 2022 2022 768 324 12% inorganic

ddd dd Revenue Actual 2023 2020 7654 352 78% organic

bbb bb Revenue Actual 2024 2024 7655 876 56% organic

ccc cc Revenue Actual 2025 2025 8778 987 43% organic with inorganic

ddd dd Revenue Actual 2025 inorganic

with organic

Outcome

23% 15% 19% 20% 12% 19% ????

xx yy zz dd cc bb Group

Thanks in advance

Zaynah

Rolling forecast

Zaynah16 — Thu, 13 Jan 2022 09:15:23 GMT

Hi Guys,

Please could someone help, I need to create a rolling forecast in a matrix with a view of the next 3 months from 2021-2025 to refrelct WP( (market Column ) and BP( Market Column ) I'd like to have business Unit and sub bu per BP and WP

Sample data

platform table

Pillar

Business Unit

Name

Region

Sub Busuiness Unit

PK_Sub BU

Sub Areas

Customer

Market

Date

Value

Thanks in advance

Zaynah

Re: LAG FUNCTION DAX

Zaynah16 — Mon, 13 Dec 2021 07:07:29 GMT

My Column seems to not be working and returned blank values ? ValtteriN

Re: LAG FUNCTION DAX

Zaynah16 — Mon, 13 Dec 2021 06:41:34 GMT

Hi ValtteriN,

Thank you for the above, would it possible for you to drop the powr bi file that you did the example in here

Thanks

Zaynah

LAG FUNCTION DAX

Zaynah16 — Thu, 09 Dec 2021 09:54:37 GMT

Hi Guys,

Please can someone help with a lag function.

Calculation needed :

Working Platform Conversion =
Buying Platform(This Year BP- LY BP )/Last year WP

Required output in BI

Single column with the value WPC

Data Structure

Table 1

pillar BU name region od name market value date

XXX xx x x xx x BP JAN2021

YYY yy x x xx x BP FEB 2021

ZZZ zz x x xx x BP MAR 2021

AAA AA x x xx x BP APR 2021

BBB BB x x xx x BP MAY 2021

CCC CC x x xx x BP JUNE 2021

XXX xx x x xx x BP JULY2021

YYY yy x x xx x BP AUG 2021

ZZZ zz x x xx x BP SEP 2021

AAA AA x x xx x BP OCT 2021

BBB BB x x xx x BP NOV 2021

CCC CC x x xx x BP DEC 2021

XXX xx x x xx x WP JAN2021

YYY yy x x xx x WP FEB 2021

ZZZ zz x x xx x WP MAR 2021

AAA AA x x xx x WP APR 2021

BBB BB x x xx x WP MAY 2021

CCC CC x x xx x WP JUNE 2021

XXX xx x x xx x WP JULY2021

YYY yy x x xx x WP AUG 2021

ZZZ zz x x xx x WP SEP 2021

AAA AA x x xx x WP OCT 2021

BBB BB x x xx x WP NOV 2021

CCC CC x x xx x WP DEC 2021

XXX xx x x xx x BP JAN2022

YYY yy x x xx x BP FEB 2022

ZZZ zz x x xx x BP MAR 2022

AAA AA x x xx x BP APR 2022

BBB BB x x xx x BP MAY 2022

CCC CC x x xx x BP JUNE 2022

XXX xx x x xx x BP JULY2022

YYY yy x x xx x BP AUG 2022

ZZZ zz x x xx x BP SEP 2022

AAA AA x x xx x BP OCT 2022

BBB BB x x xx x BP NOV 2022

CCC CC x x xx x BP DEC 2022

XXX xx x x xx x WP JAN2022

YYY yy x x xx x WP FEB 2022

ZZZ zz x x xx x WP MAR 2022

AAA AA x x xx x WP APR 2022

BBB BB x x xx x WP MAY 2022

CCC CC x x xx x WP JUNE 2022

XXX xx x x xx x WP JULY2022

YYY yy x x xx x WP AUG 2022

ZZZ zz x x xx x WP SEP 2022

AAA AA x x xx x WP OCT 2022

BBB BB x x xx x WP NOV 2022

CCC CC x x xx x WP DEC 2022

Filters across multiple tables

Zaynah16 — Fri, 26 Nov 2021 13:50:30 GMT

Hi guys,

Please help

I am trying to apply a filter across multiple tables.

I have calculated Return on Investment as below however revenue, cost Savings and cost incurred are from the annual Returns table and spend to date is from another table and therefore reflecting incorrecly. Please help

calculation

Revenue+Cost_savings-Cost_incurred/SpendToDate*100

DAX

ROI =

VAR _Revenue = CALCULATE(SUM('Annual Returns (2)'[Value]),FILTER('Annual Returns (2)','Annual Returns (2)'[Measure]="Revenue"))

VAR _Cost_savings = CALCULATE(SUM('Annual Returns (2)'[Value]),FILTER('Annual Returns (2)','Annual Returns (2)'[Measure]="Cost savings"))

VAR _Cost_incurred = CALCULATE(SUM('Annual Returns (2)'[Value]),FILTER('Annual Returns (2)','Annual Returns (2)'[Measure]="Cost incurred"))

VAR _SpendToDate = CALCULATE(SUM('SDG Goals'[Spend To Date]))

VAR _ROI = ((_Revenue+_Cost_savings-_Cost_incurred)/_SpendToDate)*100

RETURN

_ROI

Kind Regards

Zaynah

Re: How to Calculate Returns

Zaynah16 — Fri, 26 Nov 2021 13:34:23 GMT

Hi Rico,

Thank you for your assistance this has helped.

I am now trying to apply a filter across multiple tables.

Revenue+Cost_savings-Cost_incurred/SpendToDate*100

DAX

ROI =

VAR _Revenue = CALCULATE(SUM('Annual Returns (2)'[Value]),FILTER('Annual Returns (2)','Annual Returns (2)'[Measure]="Revenue"))

VAR _Cost_savings = CALCULATE(SUM('Annual Returns (2)'[Value]),FILTER('Annual Returns (2)','Annual Returns (2)'[Measure]="Cost savings"))

VAR _Cost_incurred = CALCULATE(SUM('Annual Returns (2)'[Value]),FILTER('Annual Returns (2)','Annual Returns (2)'[Measure]="Cost incurred"))

VAR _SpendToDate = CALCULATE(SUM('SDG Goals'[Spend To Date]))

VAR _ROI = ((_Revenue+_Cost_savings-_Cost_incurred)/_SpendToDate)*100

RETURN

_ROI

Re: How to Calculate Returns

Zaynah16 — Fri, 26 Nov 2021 08:04:43 GMT

Hi Rico,

Thank you for your assistance this has helped.

I am now trying to apply a filter across multiple tables.

Revenue+Cost_savings-Cost_incurred/SpendToDate*100

DAX

ROI =

VAR _Revenue = CALCULATE(SUM('Annual Returns (2)'[Value]),FILTER('Annual Returns (2)','Annual Returns (2)'[Measure]="Revenue"))

VAR _Cost_savings = CALCULATE(SUM('Annual Returns (2)'[Value]),FILTER('Annual Returns (2)','Annual Returns (2)'[Measure]="Cost savings"))

VAR _Cost_incurred = CALCULATE(SUM('Annual Returns (2)'[Value]),FILTER('Annual Returns (2)','Annual Returns (2)'[Measure]="Cost incurred"))

VAR _SpendToDate = CALCULATE(SUM('SDG Goals'[Spend To Date]))

VAR _ROI = ((_Revenue+_Cost_savings-_Cost_incurred)/_SpendToDate)*100

RETURN

_ROI

Re: How to Calculate Returns

Zaynah16 — Mon, 22 Nov 2021 13:02:42 GMT

Hi Rico,

Many Thanks for the help, I've tried the above however the code does not run and gives me the following error "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

Kind Regards

Zaynah

How to calculate a return

Zaynah16 — Thu, 18 Nov 2021 14:02:41 GMT

Hi everyone,

Please could i get help to calculate a Return for each of the 5 years from revenue, cost savings, and cost incurred.

I've included sample data as well as the output required.

Calculation

Revenue 2021 + Cost Savings 2021 - Costs incurred 2021= Return 2021

Data

Year 2021 Year 2022 Year 2023 Year 2024 Year 2025 KPI

10 000 20 0000 15 000 56 000 12 000 ( Revenue ) Decrease potable water comsuption

8 000 90000 10 000 12 000 17 000 ( Cost Savings) Decrease potable water comsuption

5 000 6 000 7 000 8 0000 9 000 ( Costs Incurred) Decrease potable water comsuption

10 000 20 0000 15 000 56 000 12 000 ( Revenue ) Decrease discharge sea to sewer

8 000 90000 10 000 12 000 17 000 ( Cost Savings) Decrease discharge sea to sewer

5 000 6 000 7 000 8 0000 9 000 ( Costs Incurred) Decrease discharge sea to sewer

Required output