Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi
I have food dataset with dim_date relationship, i have food cost, food revenue measures, and i have some calculated measures in a table;
for food cost, i have
1. A_Cost Food Ttl = SUM('fact_ed_gohr'[A_Cost Food])
2. Food LY = CALCULATE('fact_ed_gohr'[A_Cost Food Ttl],SAMEPERIODLASTYEAR('dim_Date'[date]))
3. A_Cost Food YTD = TOTALYTD('fact_ed_gohr'[A_Cost Food Ttl],dim_Date[date])
4. A_Cost Food YTD LY = TOTALYTD('fact_ed_gohr'[A_Cost Food Ttl],SAMEPERIODLASTYEAR((dim_Date[date])))
everything looks fine
for food revenue, i have
1.A_Food Revenue Ttl = SUM('fact_ed_gohr'[A_Food Revenue])*-1
2. Revenue LY = CALCULATE('fact_ed_gohr'[A_Food Revenue Ttl],SAMEPERIODLASTYEAR('dim_Date'[date]))
3.A_Food Revenue YTD = TOTALYTD('fact_ed_gohr'[A_Food Revenue Ttl],'dim_Date'[date])
4.A_Food Revenue YTD LY = TOTALYTD('fact_ed_gohr'[A_Food Revenue LY],'dim_Date'[date])
and also the result was fine;
but then i need profitability percentage which i have to divide food cost and food revenue,
1.A_Food Profitability Ttl = divide('fact_ed_gohr'[A_Cost Food Ttl] , 'fact_ed_gohr'[A_Food Revenue Ttl],0)
2. A_Food Profitability LY = CALCULATE([A_Food Profitability Ttl],SAMEPERIODLASTYEAR('dim_Date'[date]))
so far the result was fine, but when i need YTD for profitability the result was "0"
here is the formula
A_Food Profitability YTD = TOTALYTD('fact_ed_gohr'[A_Food Profitability Ttl],dim_Date[date])
A_Food Profitability YTD LY :=TOTALYTD('fact_ed_gohr'[A_Food Profitability LY],dim_Date[date])
i attached the result in power bi
what formula should i used to calculated YTD and YTD LY on that case;
Best Regards;
Vick
Solved! Go to Solution.
Hi Vick,
I copied all your formulas. No issue at all. Please check out it in the attachment. Please also change the format as the snapshot shows.
Best Regards,
Dale
Hi Vick,
It seems no errors in your formulas. Please add more digital points to the result to see if it will show something. Can you share the file? You can mask your data first.
Best Regards,
Dale
Hi Dale;
here is the link for sample dataset;
https://1drv.ms/x/s!AjuODxf7323-aUatt5spFxn-DlY
or
https://drive.google.com/drive/folders/1FdBtbEr1je5NzEN7rCKzPRoDmM56YXTI
thank you for yor attention on my issue;
warm regards;
Vick
Hi Vick,
I copied all your formulas. No issue at all. Please check out it in the attachment. Please also change the format as the snapshot shows.
Best Regards,
Dale
Hi Dale;
I've test the DAX Formula, i think i used the wrong formula for Profitability YTD and Profitability YTD LY, i tried your DAX formula for YTD and it worked;
A_Food Profitability YTD = TOTALYTD(sumx(SUMMARIZE(fact_ed_gohr, dim_Date[Date].[Year], dim_Date[Date].[Month], "dd", 'fact_ed_gohr'[A_Food Profitability Ttl]), [dd]),dim_Date[date])
i get the point that i should be summarized/group by the data, but how the logic for YTD LY; i tried this
A_Food Profitability YTD LY = TOTALYTD(sumx(SUMMARIZE(fact_ed_gohr, dim_Date[Date].[Year], dim_Date[Date].[Month], "aa", 'fact_ed_gohr'[A_Food Profitability LY]), [aa]),dim_Date[date])
the result is blank
and of course when i use my old formula, the result is wrong
A_Food Profitability YTD LY old = TOTALYTD('fact_ed_gohr'[A_Food Profitability LY],dim_Date[date])
here is the attachment
hope you can give me any clue to solved this;
thank you for you help
vick
Hi Vick,
The context of SUMMARIZE is relatively independent. We can change it like below.
A_Food Profitability YTD LY new = CALCULATE ( [A_Food Profitability YTD], SAMEPERIODLASTYEAR ( 'dim_Date'[Date] ) )
Just in case the [A_Food Profitability YTD] is as follows. You can rename the blue part to another name at the same time.
A_Food Profitability YTD = TOTALYTD ( SUMX ( SUMMARIZE ( fact_ed_gohr, dim_Date[Date].[Year], dim_Date[Date].[Month], "total", 'fact_ed_gohr'[A_Food Profitability Ttl] ), [total] ), dim_Date[date] )
Best Regards,
Dale
Hi Dale;
Yes, I resolved this issue, now everything looks fine, thank you for your help;
warm regards;
vick
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |